Recently someone posted on MSDN forum about collation related error which I have seen multiple times. Today I am taking time to write notes about that. Here is the famous collation conflict error which every DBA would encounter at least once in their career.
Msg 468, Level 16, State 9, Line 29
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Let’s understand the error first. There are two collation in the error message. If you read in a flick they look similar but they are not. First one has CS (stands for case sensitive) and second one has CI (stands for case insensitive). From the message it’s clear that SQL can’t convert value implicitly between these two collations. Next action would be to you capture a profiler trace or some other troubleshooting technique to identify offending query. In other words, we get this error when queries use two or more columns with different collations for join and comparison purposes. So there could be two possibilities of getting this error message:
-
Columns are in two different databases that have a different default collation.
-
Columns are in same database that have collation explicitly specified as different.
In most of the cases, this error has been caused when database was moved from one server to another server and this falls under first category. Here is the quick script to get same error.
CREATE DATABASE [CaseSensitiveCollation]
COLLATE SQL_Latin1_General_CP1_CS_AS
go
Use [CaseSensitiveCollation]
go
Create table MyTableInCaseSensitiveDatabase (vc varchar(100))
go
insert into MyTableInCaseSensitiveDatabase
values ('sysobjects')
go
SELECT *
FROM master.sys.objects a
INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b
ON a.NAME = b.vc
Here is the screenshot.
The collation of master database is SQL_Latin1_General_CP1_CI_AS and for column vc in the table its different and hence the error. Now, how do you resolve this error? We have multiple options.
- Help SQL Server in identifying which column we want to convert to different collation.
SELECT *
FROM master.sys.objects a
INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b
ON a.name = b.vc collate SQL_Latin1_General_CP1_CI_AS
I have seen few DBA suggesting to change database collation. It is important to understand that column collation is specified during creation of table. We can find that using below
Even if we alter the database the collation of already created table would NOT change. Only way to change the collation of existing tables is
- Move the data to a new table with new collation.
- Get the script of the table and create same index, stats etc. on new tables.
- Drop the old table
- Rename new table as old table.
If error is due to temporary tables created in tempdb database then you need to give a thought to contained database feature. Other thing Another option would be to provide the column level collation while creating table as below.
CREATE TABLE #SQLServerHelp
(iPK int PRIMARY KEY,
nCol nchar COLLATE SQL_Latin1_General_CP1_CS_AS
);
Here are other error which you might get.
- Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator for %ls operation.
- Collation conflict caused by collate clauses with different collation ‘%.*ls’ and ‘%.*ls’.
- Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator occurring in %ls statement column %d.
- Implicit conversion of %ls value to %ls cannot be performed because the resulting collation is unresolved due to collation conflict between "%ls" and "%ls" in %ls operator.
- Implicit conversion of %ls value to %ls cannot be performed because the collation of the value is unresolved due to a collation conflict between "%ls" and "%ls" in %ls operator.
- Cannot resolve the collation conflict between "%.*ls" and "%.*ls" in the %ls operation.
Hope this would help you in troubleshooting and fixing collation errors.
Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
Help: SQL Server 