If you’ve ever worked with databases from different sources, or with different collation settings, you’ve probably encountered an error like this:
Cannot resolve collation conflict between ‘Latin1_General_CI_AS’ and ‘SQL_Latin1_General_CP1_CI_AS’ in equal to operation.
The error is cause by having different collation settings for the columns in the comparison. SQL Server cannot reliably resolve how the columns should be compared to determine if they meet your conditions. For example, when you compare two columns that are both case-sensitive (Latin1_General_CS_AS) then ‘AA’ = ‘AA’, but ‘Aa’ != ‘AA’. On a case-insensitive collation ‘Aa’ = ‘AA’. Where code pages change, or the case or accent sensitivity changes, SQL Server may require you to specify which collation is to be used during comparison.
In this case the error was caused by a comparison of two columns in a join clause where the source tables were from databases with different collations (the code page was different).
Resolving the error is as simple as adding the COLLATE codepage keyword after the column. You can figure out which is the offending column and change its collation, or change both columns to the same collation.
-- Example COLLATE Fix for a JOIN condition SELECT TableA.col1, TableB.col2 FROM TableA INNER JOIN TableB ON TableA.col3 COLLATE Latin1_General_CI_AS = TableB.col3 COLLATE Latin1_General_CI_AS -- Example COLLATE Fix for a WHERE condition SELECT TableA.col1 FROM DatabaseA.dbo.TableA, dbo.TableB WHERE TableA.col3 COLLATE Latin1_General_CI_AS = Max(TableB.col2) -- Example COLLATE Fix for a WHERE IN condition SELECT TableA.col1 FROM TableA WHERE TableA.col3 COLLATE Latin1_General_CI_AS IN (SELECT TableB.col1 FROM TableB)
You can check the collation setting for a column using the sp_help TableName command.