Cannot resolve collation conflict for equal to operation

June 11, 2007 by pinaldave

Cannot resolve collation conflict for equal to operation.

In MS SQL SERVER, the collation can be set in column level. When compared 2 different collation column in the query, this error comes up.

SELECT IDFROM ItemsTableINNER JOIN AccountsTableWHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator.

SELECT IDFROM ItemsTableINNER JOIN AccountsTableWHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Collation can affect following areas:

1) Where clauses
2) Join predicates
3) Functions
4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)