月度存档: 四月 2010

Cannot resolve the collation conflict …

today I create 4 databases for a new application, and it requires case sensitive collation, so I change the collation from “Latin1_General_CI_AI” to “Latin1_General_CS_AI”, but when I create user and check “Securables”, it pump out a error window saying

…..
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the UNION to operation. (Microsoft SQL Server, Error: 468)

After investigate, I found the reason and solution,

Reason:
This will occur when your query attempts to join a user table to a temporary table, and the tempdb database collation differs from the user database collation (sp_helpdb will show you default database collation).  

Solution:
Option1: Locate the query that is raising the error, identify the join e­xpression and use the COLLATE clause to tell SQL which collation to use so that the query semantics aren’t ambiguous.

Option2: Change the collation of the temp db database to be consistent between the user defined databases.