SHARE:

SQL SERVER – How to troubleshoot collation conflict errors when executing queries

In MS SQL SERVER, the collation can be set at the column level. When you compare (or concatenate) 2 differents collation column in the query, this error occurs.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CI_AS” in the equal to operation.

How to know the collation for a specific column name?

Type this query :

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SC.collation AS "Collation" 
FROM dbo.sysobjects SO 
INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U'  and SC.NAME = 'ColumnName'
ORDER BY SO.[name], SC.colid 

Solution 1: Fix the query

Sample 1 :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 = T2.Col2

Sample 1 fixed :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 2 :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2

Sample 2 fixed :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 3 :

SELECT T1.Name + T2.Name 
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id

Sample 3 fixed :

SELECT T1.Name COLLATE DATABASE_DEFAULT + T2.Name COLLATE DATABASE_DEFAULT
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id 

As you can see, COLLATIONS can affect a query, in a concatenation in the SELECT clause, in a comparison in WHERE clause, and JOIN clause.

Solution 2: Fix the collation on the column

Query :

ALTER TABLE T1
ALTER COLUMN Col1 COLLATE SQL_Latin1_General_CP1_CI_AS

(OR DATABASE_DEFAULT for example)

The goal is to harmonize COLLATIONS in this solution.

Written by

anthonygiretti

Anthony is a specialist in Web technologies (14 years of experience), in particular Microsoft .NET and learns the Cloud Azure platform. He has received twice the Microsoft MVP award and he is also certified Microsoft MCSD and Azure Fundamentals.
%d bloggers like this: