How to Fix SQL Collation Conflict in SQL Server

Problem

The following is the error message that you can find when you tried to run a query joining two tables, one table that belongs to the database created by the software vendor and the other a temporary table, both tables joined by a common column of varchar(10) data type. This is the full error message that you can see:

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.

Cause

We configured the SQL Server instance with the SQL_Latin1_General_CP1_CI_AS because it is the standard in our organization, and that means all system databases on the server have that collation (including TempDB), but the software vendor created the new database with a different collation, collation Latin1_General_CI_AS. The conflict is originated by the difference in collation between the instance and the vendor database.

Solution

If possible change the database collation. In our case, was not possible because the vendor does not support SQL_Latin1_General_CP1_CI_AS collation. The following link gives instructions on how to change the database collation.

http://msdn.microsoft.com/en-us/library/ms175835.aspx

To change the server collation, you either have to reinstall SQL Server or rebuild system databases. You can reinstalled Microsoft SQL Server and set the server collation to the collation specified by the software vendor. For more information, please see the following article:

http://msdn.microsoft.com/en-us/library/ms179254.aspx

To allow specific queries to run despite the difference on collations, you need to modify those queries and include the COLLATE or COLLATE database_default clause when comparing string columns with different collations. Please see the following article, for more information:

http://msdn.microsoft.com/en-us/library/ms184391.aspx