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.

Collation

Hi ,
I would know what is the simplest (and the more reliable) method to convert an entire db from a collation to another...
Thanks
:confused:The simplest way to do it is to script out the schema, edit the script to change the collation, then play the script into an empty database container and reload the data. For small (under 2 Gb) databases, this works fine.

-PatP|||Thanks ... But what about changing collation id from script?
I've already seen the possibility to change collation for columns table ...
Is there no solution to change also DB setting?

Bye :cool:|||ALTER DATABASE will allow you to change the collation, but read carefully to be sure that is what you want... I suspect that it won't be much help.

-PatP|||You can use "Alter database" to change the default collation, but this will only affect tables that are created after the change. You could face a lot of collation conflicts, if you are not careful. As for getting all of the columns in all of the tables in one shot, you would probably have to write a cursor to loop through each table and column with a type of (n)char, (n)varchar, or (n)text, and change them individually. Dose this help?|||So, When I change collation ID of columns table or db this setting will affect only on new data ...

If it's right there is something that I don't understand.

Sometime I've worked with several tables inside at the same db ... these table had different collations and JOIN statement between them caused an collation error, but after executing an UPDATE to collation ID of involded columns the problems was disappeared....Why? :confused: :confused:

Maybe is there some implicit conversion between semi-compatible collations or SQL doesn't raise (after upating ID collation) an error but the result of operation can be corrupted?

Thanks|||ALTER DATABASE will change the default collation used for new character columns (if you don't explicitly specify a collation). ALTER TABLE will change the collation of existing columns.

I don't know of any a "magic wand" that will retroactively change the collation of all your existing columns with a single action.

-PatPsqlsql