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.

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>