Collation Error In SQL Sever

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation."

Don’t panic if you get this error while joining your tables. There is a simple way to solve this. It happens because of the different collation settings on two columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.

Let us assume that collation has not been set at the column level and instead at the db level. Then, we have to execute two simple statements as below.

Statements

Select DATABASEPROPERTYYEX('DB1',N'Collation')
Select DATABASEPROPERTYYEX('DB2',N'Collation')

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.

 

If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.

select * from Demo1.dbo.Employee emp
join Demo2.dbo.Details dt
on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS)