Monday, 21 February 2011

Cannot resolve the collation conflict

I've been working recently on databases that contain different collations. Mainly the following two:
  • SQL_Latin1_General_CP1_CI_AS
  • Latin1_General_CI_AS
When trying to write simple queries I kept getting the following error:

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

To solve this you just need to cast the field to the correct collatiion using COLLATE Latin1_General_CI_AS
Example.

SELECT *
FROM Table1INNER JOIN Table2

ON Table1.ID = Table2.ID
WHERE Table1.ID = 1


This would become

SELECT *
FROM Table1INNER JOIN Table2

ON Table1.ID COLLATE Latin1_General_CI_AS = Table2.ID
WHERE Table1.ID = 1

No comments: