Thursday, 17 April 2008

Cannot resolve the collation conflict T-SQL

Cannot resolve the collation conflict between XXX and YYY in the equal to operation

I recently got this whilst doing some SQL queries that involved joins between two fields with different (albeit very similar) collation types. To stop SQL server from screaming, you'll need to perform a sort of collation cast. The quickest way is to cast one of the field's collation so it matches the other, or you can cast both. Casting to the default database collation is also an option:

SELECT 
...
JOIN table t ON t.field COLLATE database_default = s.field COLLATE database_default

More info on collate from MSDN.

6 comments:

Anonymous said...

Thanks man, it works.

Anonymous said...

Hard to find syntax which works perfectly. Thanks for the post!

Andrew Gunn said...

Dude, you haven't posted anything new for over a year. Slacker!

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Web Solutions said...
This comment has been removed by a blog administrator.