Getting Msg 457 - Collation Conflict

We're trying to install a couple of stored procedures that were generated on
the United States (SQL Server 2000, SP4) onto a SQL Server in the UK (SQL
Server 2000, SP3), but we're getting message 457 about a collation conflict.
Altering MY_PROC
Server: Msg 457, Level 16, State 1, Procedure MY_PROC, Line 1291
Implicit conversion of varchar value to varchar cannot be performed because
the collation of the value is unresolved due to a collation conflict.
We're trying to figure why this happened on these two procedures. The
SQL_Latin1_General_CP1_Cl_AS collation was used for installation on the
server in the UK so that it would match our systems in the states. All of ou
r
tables have been created with specific collation clauses in the create table
statement so that we intentionally specify SQL_Latin1_General_CP1_CL_AS. The
error in the procedure occurs when trying to join a regular user table to a
table variable. The table variable did not specify the collate clause, and
Msg 457 was generated.
We were able to work around this by specifying a collate clause in the table
variable, but the question is: Where did the wrong collation come from? If
the instance and database were both set to have SQL_Latin1_General_CP1_Cl_AS
collation, how could a different collation have been picked up by default?
The server in the UK has the UK local set, but this by itself would not seem
to be a factor (at least in my own testing).
Thanks,
Rob(tm)Hi, Rob
A table variable is stored in the tempdb database, using the default
collation of this database, which is the server's default colation.
Razvan|||To add to Razvan's response, you can check the instance default collation
with SERVERPROPERTY:
SELECT SERVERPROPERTY('Collation')
This should be the same as the tempdb collation:
SELECT DATABASEPROPERTYEX('tempdb', 'Collation')
Hope this helps.
Dan Guzman
SQL Server MVP
"rapallegro" <rapallegro@.discussions.microsoft.com> wrote in message
news:CFACF25D-FB23-47CB-A74C-8C85CB2EE50C@.microsoft.com...
> We're trying to install a couple of stored procedures that were generated
> on
> the United States (SQL Server 2000, SP4) onto a SQL Server in the UK (SQL
> Server 2000, SP3), but we're getting message 457 about a collation
> conflict.
> Altering MY_PROC
> Server: Msg 457, Level 16, State 1, Procedure MY_PROC, Line 1291
> Implicit conversion of varchar value to varchar cannot be performed
> because
> the collation of the value is unresolved due to a collation conflict.
> We're trying to figure why this happened on these two procedures. The
> SQL_Latin1_General_CP1_Cl_AS collation was used for installation on the
> server in the UK so that it would match our systems in the states. All of
> our
> tables have been created with specific collation clauses in the create
> table
> statement so that we intentionally specify SQL_Latin1_General_CP1_CL_AS.
> The
> error in the procedure occurs when trying to join a regular user table to
> a
> table variable. The table variable did not specify the collate clause, and
> Msg 457 was generated.
> We were able to work around this by specifying a collate clause in the
> table
> variable, but the question is: Where did the wrong collation come from? If
> the instance and database were both set to have
> SQL_Latin1_General_CP1_Cl_AS
> collation, how could a different collation have been picked up by default?
> The server in the UK has the UK local set, but this by itself would not
> seem
> to be a factor (at least in my own testing).
> Thanks,
> Rob(tm)|||Thanks Dan and Razvan -- so you you would agree then that the local settings
on either the client or the server computer won't have any effect, yes?
Rob(tm)
"Dan Guzman" wrote:

> To add to Razvan's response, you can check the instance default collation
> with SERVERPROPERTY:
> SELECT SERVERPROPERTY('Collation')
> This should be the same as the tempdb collation:
> SELECT DATABASEPROPERTYEX('tempdb', 'Collation')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rapallegro" <rapallegro@.discussions.microsoft.com> wrote in message
> news:CFACF25D-FB23-47CB-A74C-8C85CB2EE50C@.microsoft.com...
>
>|||> Thanks Dan and Razvan -- so you you would agree then that the local
> settings
> on either the client or the server computer won't have any effect, yes?
The Windows settings controls which collation the SQL Server installer uses
by default. IIRC, the default collation is Windows Latin1_General_CI_AS on
a UK server. The default is SQL_Latin1_General_CP1_CI_AS on a US box.
Consequently, you will run into the collation conflict problem unless
SQL_Latin1_General_CP1_CI_AS was explicitly specified during the UK SQL
Server install.
I suggest you double check the UK server instance collation with
SERVERPROPERTY('Collation'). That would explain the reason for the error.
Hope this helps.
Dan Guzman
SQL Server MVP
"rapallegro" <rapallegro@.discussions.microsoft.com> wrote in message
news:B6142FE7-17DD-4DFC-BD56-87FCA127FDA9@.microsoft.com...[vbcol=seagreen]
> Thanks Dan and Razvan -- so you you would agree then that the local
> settings
> on either the client or the server computer won't have any effect, yes?
> Rob(tm)
> "Dan Guzman" wrote:
>