SQL Cannot resolve the collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation.

For example query

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode = l.PostalCode

results in error

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

The solution is to use COLLATE in the line where it fails. In my case it was PostalCode field
Updated query:

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode COLLATE DATABASE_DEFAULT = l.PostalCode COLLATE DATABASE_DEFAULT

More complex example, where COLLATE had to be used not only in basic comparison

SELECT Name FROM dbo.DealerProfiles DP
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN 
(
	SELECT DealerName COLLATE DATABASE_DEFAULT
	FROM GEO.dbo.Dealers GD
	WHERE DP.Name COLLATE DATABASE_DEFAULT = gd.DealerName COLLATE DATABASE_DEFAULT
)

Leave a Reply

Your email address will not be published. Required fields are marked *