Collation error occurs while retrieving or updating data in Sql Server

While retrieving or updating data in Sql Server, following error occurs. "Cannot resolve the collation conflict between 'Latin1_General_BIN' and 'Latin1_General_CI_AS' in the equal to operation"

Aug 29, 2017
Knowledge

Title

URL Name

Collation-error-occurs-while-retrieving-or-updating-data-in-Sql-Server

Article Number

000141431

Environment

Product: Connect for ODBC SQL Server Wire Protocol driver Version: All supported versions OS: All supported platforms Database: SQL Server Application: All supported applications

Question/Problem Description

A JOIN query fails with below error when the columns of the tables, which are used in the comparison of the join expression have different collations defined at the column level.

Steps to Reproduce

1. Create a table T1 with a column C1 and set collation of C1 to "Latin1_General_BIN" 2. Create a table T2 with a column C2 and set collation of C2 to "Latin1_General_CI_AS" 3. Execute below SELECT statement SELECT C1 from T1 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C2 WHERE C1 not null

Clarifying Information

When the schema of the table is changed to have the same collation for the column(s) involved in the join comparison, collation error does not appear anymore.

Error Message

37000: [DXSTRO ][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Latin1_General_BIN" and "Latin1_General_CI_AS" in the equal to operation.<468> E 5804 0712-07:10:00 internal Statement errors: 37000: [DXSTRO ][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.<8180>

Defect Number

Enhancement Number

Cause

Tables with a related column present in the query have different collations at the column level. The binary collation is set at the column level.
One column has "Latin1_General_BIN" collation and the other column has "Latin1_General_CI_AS" collation.

Resolution

Use the collation name after the column name in the query.

For example, consider collation of T1.C1 is "Latin1_General_BIN" and T2.C1 is "Latin1_General_CI_AS",

SELECT T1.C1
from T1 
RIGHT OUTER JOIN T2 ON T1.C1 = T2.C2 COLLATE Latin1_General_BIN
WHERE T2.C2 is not null

Workaround

None at this time.

Notes

1. To find the collation of columns, below statement can be used.

SELECT
col.name, col.collation_name
FROM 
sys.columns col
WHERE
object_id = OBJECT_ID(<TableName>)

2. Collation error may occur in the other databases including Oracle and DB2 because of the column level collation differences. The resolution for this issue in Oracle and DB2 is also similar to the resolution mentioned in this article considering the syntax changes need to apply for the specific databases.

Keyword Phrase

Last Modified Date

9/5/2017 5:51 AM

Collation error occurs while retrieving or updating data in Sql Server

Files(0)

Was this article helpful?

Was this article helpful?

Collation error occurs while retrieving or updating data in Sql Server

Information

Related Articles