COLLATE DATABASE_DEFAULT
This is a work-around, and a dangerous one, but it works in a pinch. If you are getting collation errors such as “Cannot resolve collation conflict for equal to operation” bubbling up from SQL Server through your application, you are likely trying to JOIN two datasets of incompatible collations.
The quick and dirty workaround is to COLLATE on the fly just as you would CAST a variable for a read but not a write. So you’ll do it in a SELECT statement, which I am happy to report works just fine.
Sample problem:
SELECT Trusty.Age, Shifty.Height FROM MyGoodDataSource AS Trusty INNER JOIN SketchyDataSource AS Shifty ON Trusty.LastName = Shifty.LastName
ERROR! “Cannot resolve collation conflict for equal to operation”
So we’re going to CHOOSE TO INTERPRET THE RESULTS differently for just one of the datasources. What makes this work is that if you, like me, have been frustrated by this error to the point that you’re reading blogs about it, you have probably never changed the collation on anything. This means that everything you own is still set to the DATABASE_DEFAULT collation, so now we have a highly likely short road to success–no need to go spelunking in the depths of the server to find out what the Hell collation is in use.
Work-around for sample problem:
SELECT Trusty.Age, Shifty.Height FROM MyGoodDataSource AS Trusty INNER JOIN SketchyDataSource AS Shifty COLLATE DATABASE_DEFAULT ON Trusty.LastName = Shifty.LastName
There are dangers. You can throw a COLLATE clause on either of both inputs to the join, to the result of the join, or tho the result of the select itself. To determine which is appropriate, you need to know the basics of collation and you need to be good and familiar with your data.
But just as a workaround to get you back in business, identify the JOIN source which comes from somewhere else, and throw the COLLATE right after that (or its alias, like the “AS Shifty” above).
