SQL Server Error – Cannot resolve collation conflict for equal to operation

Resolve collation conflict error in SQL Server

This error comes up when you try to compare two different collation column in the query. To resolve collation conflict error you can use COLLATE DATABASE_DEFAULT option while comparing two different collation in select query.

In my case, if I run the below select query in SQL Server using SSMS.  It gives error :Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.

SELECT * FROM EmployeeDtl
INNER JOIN ProjectDtl
WHERE EmployeeDtl.Emp_id = ProjectDtl.Emp_id
We had investigated and found difference in collation, Collation of EmployeeDtl.Emp_id column was “SQL_Latin1_General_CP1_CI_AS” and Collation of ProjectDtl.Emp_id column was “SQL_Latin1_General_CP1_CS_AS”
.  Here we are comparing two different collation and resulted in error.

FIX : Cannot resolve collation conflict for equal to operation.

To resolve this collation conflict, we added COLLATE DATABASE_DEFAULT keyword after column name. Refer the below example

Example:

SELECT * FROM EmployeeDtl
INNER JOIN ProjectDtl
WHERE EmployeeDtl.Emp_id COLLATE DATABASE_DEFAULT  = ProjectDtl.Emp_id COLLATE DATABASE_DEFAULT
This worked for us and problem got fixed.

Leave a Reply

  

  

  


− 6 = 2