The Problem

At a customer, I had to gather data from two databases. So I started writing a SQL query that combined data from two databases and wanted to join them on an ID field. Then this popped up: “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_BIN” in the equal to operation“. Apparently along the way they decided, or by accident, switched collations.

The Solution

It’s actually very simple, you have to explicitly specify the collation in your query. I posted the code because it’s too lengthy and error-prone to write it manually several times a day…

SELECT p.KeyField
FROM DB1.PrimaryTable p
INNER JOIN DB2.SecondaryTable s ON LTRIM(RTRIM(p.KeyField))COLLATE SQL_Latin1_General_CP1_CI_AS = s.KeyField

That should do the trick!