“Cannot Resolve the collation conflict between” error in Sql Server 2005

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] COLLATE Latin1_General_CI_AS = olod.DogName

So the Dogs.Name column will be compared to the other tables column with the same collation… And no error. :o)