Error: Cannot resolve the collation conflict

This error started occurring after one of my SQL Server 2008 databases had been restored:

Msg 468, Level 16, State 9 …

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.



Supposedly, this type of error can occur when the tempdb collation does not match the current database collation — especially if using temp tables.

The easiest way to deal with this is to cast one of the field’s collation so it matches the other, or you can cast both if you need. I’m not sure if this is best practice in the long run; however, if you’re just doing some ad-hoc data mining then this is definitely a quick fix:

INNER JOIN [TABLE1].FIELD1 AS EA

ON EA.SERIAL_NBR COLLATE database_default = E.SERIAL_NBR COLLATE database_default