I’ve come across this issue a few times. You try and compare two columns on different tables where the collation has not been defined consistently for whatever reason, and you get an error like this
Msg 468, Level 16, State 9, Line 21 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
I’ve got an example below that you can run to cause the error. Notice that the Dogs.Name column is set to a collation of “SQL_Latin1_General_CP1_CI_AI” and the OtherListOfDogs.DogName column is set to a collation of “Latin1_General_CI_AS”.
DECLARE @Dogs TABLE ( DogID int ,Age int ,[Name] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI )
DECLARE @OtherListOfDogs TABLE ( DogName NVARCHAR(100) COLLATE Latin1_General_CI_AS )
SELECT * FROM @Dogs d JOIN @OtherListOfDogs olod ON d.[Name] = olod.DogName
The two columns are compared in the select statement. The collations are different and they are incompatible, thus the error.
To allow the code to work (notice I said work, not necessarily “fix”) you can add a collate statement to one of the columns (or both) like in the following:
SELECT * FROM @Dogs d JOIN @OtherListOfDogs olod ON d.[Name]= olod.DogName
So the Dogs.Name column will be compared to the other tables column with the same collation… And no error. :o)