Cannot Resolve Collation Conflict

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

Author

Admin

comments powered by Disqus

Categories

Recent Tweets

"Setting up and Getting Started with Power BI Embedded | Data Exposed" channel9.msdn.com/Shows/Data-Exp… #webcast #feedly
Retweeted by @dyardy @scottgu @victoriabeckham Worlds collide, double take on that photo!
@scottgu @victoriabeckham Worlds collide, double take on that photo!
@projectedxyz It is really easy, what are you doing (isn't this data which on the web)