collation conflict sql server

Question

When i issue a SQL query, sometimes I get the following error message:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I often solve this problem just make both table collation same. so i need to know is there any quick way to fix this issue.

I'd like to put something special in my SQL query as a result if collation is not same for both table in sql query then also query will work perfectly without any error. is there any solution?

Answer 1

You can force which collation by using the COLLATE clause.

i.e.

SELECT *
FROM Table1 T1
INNER JOIN Server2.dbo.Table2 T2
ON T1.Name = T2.Name COLLATE database_default

Collation conflicts are common when joining tables between two databases or servers, especially if the version of the DB is different.

Answer 2

You can specify a collation in a query using the collate clause:

where  col1 = col2 collate Latin1_General_CI_AS