“Cannot resolve collation conflict for equal to operation” – huh?
When joining fields in different collated databases you can see the above error message. In my particular case I had two databases with different collations and I was doing a join across the databases on the fields.
The resolution was to add the keywords “COLLATE DATABASE_DEFAULT” near the equal ‘=’ signs.
SELECT p1.BEEF_1 FROM dbo.PHEN p1 INNER JOIN
Database2.dbo.PHEN p2 ON p1.BEEF_1 COLLATE DATABASE_DEFAULT =p2.BEEF_1 COLLATE DATABASE_DEFAULT AND
p1.BEEF_2 COLLATE DATABASE_DEFAULT=p2.BEEF_2 COLLATE DATABASE_DEFAULT
Collation can affect where clauses, join predicates, functions and databases.
More about Collation
A Collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish or an alphabet such as Latin1_General (the Latin alphabet used by western European languages).
Each SQL Server collation specifies three properties: sort order for Unicode data types (nchar, nvarchar, ntext), the sort order for non-Unicode character types (char, varchar and text) and finally the code page used to store non-Unicode character data. A SQL Server collation defines how the database engine stores and operates on character and Unicode data.