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