Collation

Apr 102015 Posted in DBCC0 Responses

DBCC CHECKCONSTRAINS & Error 446

On a customer database, on an empty table, DBCC CHECKCONSTRAINTS failed with the below message:

Msg 446, Level 16, State 11, Line 1
Cannot resolve collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AI” in CASE operator for DISTINCT operation.
Msg 2509, Level 16, State 2, Line 1
DBCC CHECKCONSTRAINTS failed due to an internal query error. Please run DBCC CHECKDATABASE to ensure your data consistency.

According to MSDN, indeed a temp table is created on tempdb. So I suspected (and still am) a coding bug: when database collation is different from the server one, in case of a join on char types columns, the collation should be specified.

Read More…