“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. For example: 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.