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.
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!