collation conflict

I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?
Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:

> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Thanks all. It was indeed the collate on tempdbs.