MSSQL Server – Cannot resolve collation conflict

In MS-SQL server when you encounter cannot resolve collation conflict you are comparing two columns which have different collation for the column or table. Collation conflict can come when collation is different between two tables or columns or databases. Collation is nothing but rules to compare data based on character set of the data. Collation conflict generally comes for char, varchar etc.

Lets say you have two tables tb1 and tb2 , both have different collations. Now if you have query like

select * from tb1 join tb2

on tb1.columncollate1 = tb2.columncollate2

where tb1.columncollate3 like tb2.columncollate4

Now lets say column 1 and 3 has collation as X and column 2 and 4 has collation Y.

You can resolve this collation conflict by

1. select * from tb1 join tb2

on tb1.columncollate1 = tb2.columncollate2 COLLATE X

where tb1.columncollate3 like tb2.columncollate4 COLLATE X

Or

2. select * from tb1 join tb2

on tb1.columncollate1 COLLATE DATABASE_DEFAULT = tb2.columncollate2 COLLATE DATABASE_DEFAULT

where tb1.columncollate3 COLLATE DATABASE_DEFAULT like tb2.columncollate4 COLLATE DATABASE_DEFAULT

3 thoughts on “MSSQL Server – Cannot resolve collation conflict

  1. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.

  2. 301 Moved Permanently I was suggested this blog by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my problem. You are amazing! Thanks! your article about 301 Moved PermanentlyBest Regards Lawrence

  3. Now, this article is one thing that you do not see regularly. It’s a great thing that I saw this article thanks to my companion who revealed it to me. It is very extraordinary for a writer to spend a good number of effort and time imparting the strategies and information that you provided. It is quite noticeable that you put many time and tough work into this so thanks for your insight!

Leave a Reply

Your email address will not be published. Required fields are marked *