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;

image thumb11 Cannot resolve the 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;

image thumb12 Cannot resolve the collation conflict


Tags: , , ,
Comment (RSS)  |  Trackback

5 Comments

  1. Suhas says:
    February 2, 2012 at 2:32 pm

    Hey Nice Article.

    My problem is a bit related to the one mentioned. I use a temporary table in SQL to get some complex table. Then finally to show the output I query this temp table and some other tables in my database.

    The problem for me is that my temp table is taking, by default, the collation of the SQL Server which has a collation different from that of my database.

    How can i solve this…since i have used many temp tables in my database and all of them shout error in unison when such collation issues occur :(

  2. Rhys says:
    February 2, 2012 at 3:09 pm

    Hi Suhas,

    You can specify the collation when you create the temp table link.

    Cheers,

    Rhys

  3. Suhas says:
    February 3, 2012 at 5:56 am

    Hi Rhys,

    Thanks for the quick reply.

    Thats a prospective solution but its a really a long n time consuming process since i gotta find the temp table usage in my 1000+ procedures & replace them.

    I was just wondering if there can be better solution.

    I have currently changed the collation of the database itself and told my collagues to check up on the Collation options the next time they install SQL Server on a system.

    Thanks for the quick reply.

    God Bless and have smiling roads ahead.

    :) Suhas Pandit

  4. Rhys says:
    February 3, 2012 at 9:27 am

    Hi Suhas,

    That’s all that comes to mind apart form changing the server collation itself which won’t be fun – http://msdn.microsoft.com/en-us/library/ms179254.aspx

    It helps having a standard build document explicitly stating the collation.

    Rhys

  5. Suhas says:
    February 4, 2012 at 6:02 am

    Hi Rhys.

    Hmmm….Thanks for the reply. Thats what I’ll do.

    As of existing SQL Server installations….will start changing the collation of temp tables.

    Thanks, God Bless n have smiling roads ahead

    :) Suhas Pandit

Leave a Reply

 
«
»