How to resolve the error message 'Cannot resolve collation conflict for equal to operation'?

Products

• Cleverpath Reporter

Issue/Introduction

How to resolve the error message 'Cannot resolve collation conflict for equal to operation'?

Environment

Release:
Component: CVPRPT

Resolution

In Microsoft Sql Server, collation can be set at the column level.

This error message will be observed when comparing two columns that have different collations.

For example:

 SELECT ID
 FROM ItemsTable
 INNER JOIN AccountsTable
 WHERE ItemsTable.CollationOneCol = ItemsTableCollationTwoCol

If ItemsTable.CollaationOneCol and ItemsTableCollationTwoCol have different collation, it will generate the error message "Cannot resolve collation conflict for equal to operation".

To resolve the collation conflict at the query level, add the following keywords around the "=" operator.

 SELECT ID
 FROM ItemsTable
 INNER JOIN AccountsTable
 WHERE ItemsTable.CollationOneCol COLLATE DATABASE_DEFAULT = ItemsTable.CollationTwoCol
 COLLATE DATABASE_DEFAULT

Collation can affect the following areas:

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