Cannot resolve collation conflict for equal to operation

Recently I ran into an issue in MS SQL when ever I tried to run an sp. I was getting an error message like ‘Cannot resolve collation conflict for equal to operation’. Anyways I got the solution. So thought of including a quick note on the issue.

This issue can occur in the following areas.
Collation can affect following areas:

While using tempdb
Join statements
UDF (My issue was in here)
Where portions in queries

This issue occurs when the collation of the column that are being compared like in ‘=’ or ‘<’ or ‘>’ or ‘like’ are different. So if we change the collation of one of the column to that of the other we can remove this issue.
Like:
where table1.[column] collate SQL_Latin1_General_Cp437_CI_AI = table2.[column]

Here we changed the collation of the table.column to that of table2.column.

The best way to remove this kind of error is to set the collation of the columns that are being used to the database default. This can be done by using like below

WHERE table1.[column] COLLATE DATABASE_DEFAULT
= table2.[column] COLLATE DATABASE_DEFAULT

In case of this issue while using temporary tables, it can be because the db is in another collation with that of the temp db. So while using tempdb in where clause or joins, we just have to change the collation of the temp db to the database default.

So,

If this error msg pops up better use COLLATE DATABASE_DEFAULT

Cheers…

Advertisement