Bookmark and Share

Tuesday, May 10, 2011

Sql server: cannot resolve collation conflict for equal to operation.

Sometimes you may encounter a “collation problem” when you compare two colums from different table or database.

As MSDN says “Collations let users sort and compare strings according to their own conventions”, but they can be a really pain for developers.

The simplest way for comparing two columns with different collation without hard writing specific collation codes is to convert the two columns to the database default collation.

If this sql script gives you the error “cannot resolve collation conflict for equal to operation”

select * from people,city 
where people.citycode = city.citycode

You can transform the select adding collation instructions:

select * from people,city 
where people.citycode COLLATE DATABASE_DEFAULT = city.citycode COLLATE DATABASE_DEFAULT


Hope it helps!