Cannot resolve the collation conflict

One possible cause of the “Cannot resolve the collation conflict” error message is that your database collation doesn’t match the TempDB Collation.

Recommendation for cannot resolve the collation conflict between

It is recommended that the collation of your database match the collation TempDB. It is possible that if your collations do not match that you may get errors, or that queries using TempDB will not run correctly.
If you use temp tables and your database collation doesn’t match the TempDB collation you will get an error similar to the following error when you attempt to join to Temp Tables:

Cannot resolve the collation conflict between

Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Example Code

First to try the example, you need to check the collation of TempDB.

SELECT DATABASEPROPERTYEX('tempdb', 'Collation');

Which will look something like this (actual collation may vary).

Then you need a database that has a different collation from that of TempDB.

CREATE DATABASE [CollationTest]
 COLLATE Latin1_General_CI_AS;

or if TempDB is Latin1_General_CI_AS then make it different from TempDB

CREATE DATABASE [CollationTest]
COLLATE SQL_Latin1_General_CP437_CI_AS;

Once you have a database with a different collation, you need some data to work with.  In this example, I am going to use the table from my CTE presentation.

use [CollationTest];
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

-- insert top level departments
insert into [Departments] (id, department, parent)
 values (1, 'Camping', null);
insert into [Departments] (id, department, parent)
 values (2, 'Cycle', null);
insert into [Departments] (id, department, parent)
 values (3, 'Snowsports', null);
insert into [Departments] (id, department, parent)
 values (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent)
 values (5, 'Tents', 1);
insert into [Departments] (id, department, parent)
 values (6, 'Backpacks', 1);
insert into [Departments] (id, department, parent)
 values (7, 'Sleeping Bags', 1);
insert into [Departments] (id, department, parent)
 values (8, 'Cooking', 1);

-- now some sub-departments for cycle
insert into [Departments] (id, department, parent)
 values (9, 'Bikes', 2);
insert into [Departments] (id, department, parent)
 values (10, 'Helmets', 2);
insert into [Departments] (id, department, parent)
 values (11, 'Locks', 2);

-- now some sub-departments for snowsports
insert into [Departments] (id, department, parent)
 values (12, 'Ski', 3);
insert into [Departments] (id, department, parent)
 values (13, 'Snowboard', 3);
insert into [Departments] (id, department, parent)
 values (14, 'Snowshoe', 3);

-- now some sub-departments for fitness
insert into [Departments] (id, department, parent)
 values (15, 'Running', 4);
insert into [Departments] (id, department, parent)
 values (16, 'Swimming', 4);
insert into [Departments] (id, department, parent)
 values (17, 'Yoga', 4);

Now we check the collations to confirm that they are different.

use [CollationTest];
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') as TempDBCollation,
 DATABASEPROPERTYEX('CollationTest', 'Collation') as ThisDBCollation;

Lets see where it breaks:

use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
  FROM [Departments] d
 INNER JOIN #TempDepartments td
    ON d.department = td.department;

DROP TABLE #TempDepartments;

Which will produce the following error:

Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

How do we fix it?

You could change your database collation to match what TempDB is using, or you could specify the collation in the query.

use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
 FROM [Departments] d
 INNER JOIN #TempDepartments td
    ON d.department = td.department COLLATE Latin1_General_CI_AS;

DROP TABLE #TempDepartments;

Another option would be to use the database default collation on both.

use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
FROM [Departments] d
INNER JOIN #TempDepartments td
   ON d.department COLLATE DATABASE_DEFAULT
   = td.department COLLATE DATABASE_DEFAULT;

DROP TABLE #TempDepartments;

Keep in mind that it is recommended that the collation of your database match that of TempDB, but if they don’t you can use the above alternatives to make it work.

Similar errors:

Cannot resolve the collation conflict between “sql_latin1_general_cp437_ci_as” and “latin1_general_ci_as” in the equal to operation.
Cannot resolve the collation conflict between “sql_latin1_general_cp437_ci_as” and “sql_latin1_general_cp437_ci_as” in the union operation.

Additional Details

The error “Cannot resolve the collation conflict between…” typically occurs in SQL Server when there is a mismatch between the collation settings of two database objects (e.g., tables, columns, or literals) that are involved in a query. Collation determines how string data is sorted and compared, and if two objects have different collations, SQL Server cannot process them directly in operations like joins, comparisons, or unions without explicit handling.

Common Scenarios Where This Error Arises:

1. Joining Columns from Different Collations:
   When two tables with different collation settings are joined on string columns.Example:
   SELECT *
FROM TableA
JOIN TableB ON TableA.Name = TableB.Name
   If TableA.Name and TableB.Name have different collations, the query will fail.
2. String Comparison in Queries:
   Comparing a literal string with a column in a different collation.Example:
   SELECT *
FROM TableA
WHERE Name = 'John'
   If the column Name uses a collation that differs from the server or literal default collation, an error may occur.
3. Union or Intersect Operations:
   Combining results from multiple queries where columns have different collations.Example:
   SELECT Name
FROM TableA
UNION
SELECT Name
FROM TableB
4. If Name columns in both tables have different collations, this will trigger the conflict.

How to Resolve:

1. Explicitly Specify Collation in the Query:
   Use the COLLATE keyword to align the collations of conflicting objects within the query.Example:
   SELECT *
FROM TableA
JOIN TableB ON TableA.Name COLLATE SQL_Latin1_General_CP1_CI_AS = TableB.Name
2. Ensure Uniform Collation Across Objects:
   Modify the database or table schema to use a consistent collation for all string columns.Example:
   ALTER TABLE TableA ALTER COLUMN Name NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS;
3. Set Database Default Collation:
   Align database collation during creation or migration to avoid conflicts.
4. Check Collation Programmatically:
   Use system views to identify mismatched collations.
   SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName';
5. Consult Experts:
   For complex cases or significant schema updates, consulting a SQL Server specialist can save time and prevent issues.

How Stedman Solutions Can Help:

At Stedman Solutions, we specialize in troubleshooting and resolving collation conflicts as part of our SQL Server Managed Services. Whether you need help identifying mismatched collations, restructuring your database, or designing a collation strategy for scalability, our team of SQL Server experts is here to assist. Contact us today for efficient, reliable solutions: Contact Us.

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!