Collation Errors Running a SQL Query

If you are ever trying to join tables from different databases that are using different collations you may experience the dreaded…

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

The fix for this is fairly simple. In the join part of the query around the = parameter you can enter this code which collates the columns of the join as per the database default.

COLLATE DATABASE_DEFAULT

Below is an example of a query with the collate syntax added for your reference.

select *
FROM
LocalCustomer lc
JOIN custtodelete cd on lc.custnumber COLLATE DATABASE_DEFAULT = cd.custnumber COLLATE DATABASE_DEFAULT

I find this way simpler than the other which requires you to specify the actual collation that you wish to use in your join.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.