Cannot resolve the collation conflict

Posted on June 12, 2009, 5:57 pm, by Rhys, under T-SQL.

I do a fair bit of work with Linked Servers and cross-database queries  and sometimes come across the following error when joining between databases with different collations;

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_Pref_CP850_CI_AS'; in the equal to operation.

To replicate this error run the below TSQL to create two databases with tables and data. 

?View Code TSQL
-- Create first database
CREATE DATABASE database1 COLLATE Latin1_General_CI_AS;
GO
-- Create second database
CREATE DATABASE database2 COLLATE SQL_Latin1_General_Pref_CP850_CI_AS;
GO
 
USE database1;
GO
-- Create Customer table in database1
CREATE TABLE Customer
(
	CustID INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	FirstName VARCHAR(30) NOT NULL,
	LastName VARCHAR(30) NOT NULL,
	DOB DATETIME NOT NULL,
	StartDate DATETIME NOT NULL DEFAULT GETDATE()
); 
GO
USE database2;
GO
-- Create Customer table in database2
CREATE TABLE Customer
(
	CustID INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	FirstName VARCHAR(30) NOT NULL,
	LastName VARCHAR(30) NOT NULL,
	DOB DATETIME NOT NULL,
	StartDate DATETIME NOT NULL DEFAULT GETDATE()
); 
GO
 
USE database1;
GO
-- Insert test data
INSERT INTO dbo.Customer
(
	FirstName,
	LastName,
	DOB
)
SELECT 'Joe', 'Bloggs', '1975-01-01 00:00:00'
UNION ALL
SELECT'Dave', 'Smith', '1977-10-11 00:00:00'
UNION ALL
SELECT'Fred', 'Bloggs', '1965-11-28 00:00:00'
UNION ALL
SELECT'Sue', 'Smith', '1974-06-17 00:00:00'
UNION ALL
SELECT 'Steve', 'Smith', '1981-07-07 00:00:00';
GO
USE database2;
GO
-- Insert test data
INSERT INTO dbo.Customer
(
	FirstName,
	LastName,
	DOB
)
SELECT 'Joe', 'Bloggs', '1975-01-01 00:00:00'
UNION ALL
SELECT'Dave', 'Smith', '1977-10-11 00:00:00'
UNION ALL
SELECT'Fred', 'Bloggs', '1965-11-28 00:00:00'
UNION ALL
SELECT'Sue', 'Smith', '1974-06-17 00:00:00'
UNION ALL
SELECT 'Steve', 'Smith', '1981-07-07 00:00:00';
GO

Run the following query to observe the collation conflict.

?View Code TSQL
-- Cross-database query causing the collation conflict
SELECT *
FROM dbo.Customer c1
INNER JOIN database1.dbo.Customer AS c2
	ON c1.FirstName = c2.FirstName
AND c1.LastName = c2.LastName;

SQL Server collation conflict

You could fix this by changing the collation in one of the databases, i.e.

?View Code TSQL
-- Set database2 to the same collation as database1
ALTER DATABASE database2 COLLATE Latin1_General_CI_AS;
-- Change VARCHAR columns on our existing tables
ALTER TABLE Customer ALTER COLUMN FirstName VARCHAR(30) COLLATE Latin1_General_CI_AS;
ALTER TABLE Customer ALTER COLUMN LastName VARCHAR(30) COLLATE Latin1_General_CI_AS;

Sometimes you may not want, or be able, to change a database in this way. In these situations you can add COLLATE DATABASE_DEFAULT to the JOINS or expressions in your query.

?View Code TSQL
-- Using COLLATE DATABASE_DEFAULT
SELECT *
FROM dbo.Customer c1
INNER JOIN database1.dbo.Customer AS c2
	ON c1.FirstName = c2.FirstName COLLATE DATABASE_DEFAULT
AND c1.LastName = c2.LastName COLLATE DATABASE_DEFAULT;

Query using COLLATE DATABASE_DEFAULT

Tags: , , ,
Comment (RSS)  |  Trackback

Leave a Reply

«
»