Ever since I have started writing about Error Messages a number of you ping me from time to time to understand why you are getting an error. As I always say, Error messages are a great way to learn concepts and we become better if we understand the reason behind every error one gets. As I scan through almost every single mail that comes in, some of them catch my attention and I tend to write this experience back as a blog post.
Last week, one fresher who was working for an European client wrote back to me with an error mentioned below:
Msg 468, Level 16, State 9, Line 15
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.
Though the error looked simple and self-explanatory, this kid wanted to mimic this error in his environment. He also added that he was using temp tables while this error surfaced. This statement got me interested and I wanted to show him with a simple script to how this error can be achieved.
Let me start by knowing the server collation to start with:
-- Server Collation
SELECT SERVERPROPERTY('Collation')
——————————————————————–
SQL_Latin1_General_CP1_CI_AS
As you can see, I am using the default collation on my server at this moment. Now let us create an DB with a different collation (French) for example. We will also create two tables post that. First will be a normal table while the second one will be an Temp table.
-- Create database with different collation
CREATE DATABASE [DB_Not_Contained]
CONTAINMENT = NONE
COLLATE French_CS_AI
GO
-- Create 2 tables with 1 being a temp table so it goes to tempdb and uses the server collation and not the database collation
USE [DB_Not_Contained]
GO
CREATE TABLE [DemoCollation]
(DemoCollationNM VARCHAR(100))
GO
CREATE TABLE #DemoCollation
(DemoCollationNM VARCHAR(100))
-- Insert records into both tables
INSERT dbo.DemoCollation
(DemoCollationNM)
VALUES ('Test Join');
INSERT #DemoCollation
(DemoCollationNM)
VALUES ('Test Join');
Now that the basic setup of data is over. Let us make a simple join between these two tables.
-- Now query and try and join both tables having 2 different collations
SELECT p.DemoCollationNM
FROM dbo.DemoCollation p
INNER JOIN #DemoCollation d ON
p.DemoCollationNM = d.DemoCollationNM
Woot, we will receive the error message:
Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.
Whenever you encounter this error message, please go ahead and check the collation of tables under question to be clear they are not different.
So what are the learnings from these simple steps?
- We cannot join tables, columns when there is conflict in collation between the objects
- Temp table uses the same collation as our Server by default, so are the objects that are created.
I know I have given you a hint on how you might potentially mitigate this, but let me see if anyone can give me the solution to this problem and how have you ever been able to solve these? Have you ever encountered this error?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Hi Pinal,
I have come across the same scenario in one of our projects where we tried to query by joining a temp table where collation of temp db was different from that in the user database we were querying. Temp tables by default would follow the collation settings as that of Temp db and to address this we used:
— Now query and try and join both tables having 2 different collations
SELECT p.DemoCollationNM
FROM dbo.DemoCollation p
INNER JOIN #DemoCollation d ON
p.DemoCollationNM = d.DemoCollationNM COLLATE DATABASE_DEFAULT
As seen in MSDN BOL:
The ‘database_default’ option in the COLLATE clause specifies that a column in a temp table uses the collation default of the current user database for the connection instead of tempdb.
Please correct if my understanding is wrong.
You are right Vignesh.
Perfect It works for me
Thanks to all.
Method 1:(using Collate while creating temp table)
CREATE TABLE #DemoCollation
(DemoCollationNM VARCHAR(100) COLLATE French_CS_AI)
Method 2:(using Collate while joining the temp table)
SELECT p.DemoCollationNM
FROM dbo.DemoCollation p
INNER JOIN #DemoCollation d ON
p.DemoCollationNM = d.DemoCollationNM COLLATE French_CS_AI
Correct Yuvaraj.
Hi Pinal,
This is a common problem when we compare character type data of two different site locations databases. The error can be resolved by using COLLATE clause to a character string expression (here equality in JOIN operator) to apply a collation cast.
So the query would be written as,
1. Explicitly writing collation name while casting
SELECT P.DemoCollationName
FROM DemoCollation AS P
INNER JOIN #DemoCollation AS D
ON P.DemoCollationName = D.DemoCollationName COLLATE French_CS_AI;
GO
2. Using database default
SELECT P.DemoCollationName
FROM DemoCollation AS P
INNER JOIN #DemoCollation AS D
ON P.DemoCollationName = D.DemoCollationName COLLATE DATABASE_DEFAULT;
GO
Regards,
Hitesh Shah
Correct Hitesh. Thanks for your comment.
I have had this error and to completely cure it you have to go back and set up the database from scratch again. But you can work around by converting fields using CAST during the link.
So that the data types match each other?
Paul – So that the “collation” match each other?
Please use solution above posted by Hitesh – this was what I was thinking of
Hai pinal,
One query.
Is there anyway to drop user defined data types in sql server?
Sorry for posting this comment in this place. Dont know how to approach you.
Thanks,
Srikar
If its not referenced by any other object then you can drop it. Else you would get error 3732. Below is a sample
Cannot drop type ‘dbo.AccountNumber’ because it is being referenced by object ‘Vendor’. There may be other objects that reference this type. (Microsoft SQL Server, Error: 3732)
I already droped all the objects which depends on this user defined type. But still Im unable to drop the user defined type. I did not get solution even when i searched in web. Please assist.
have you tried “View dependencies” option in SSMS?
You can use DROP command
DROP type type_name
Thank you Pinal interesting point
Manoj – I am glad that you liked it.
Thank you fr the post, interesting for me.
Manoj – I am glad that you liked it.
Thanks for this information Sir Pinal
Thanks Al
Hi Pinal Dave,
I find it impossible to catch error 468 in a try catch block.
example code
CREATE PROCEDURE [dbo].[usp_TestError468] AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
begin try
create table #temp2(quote_id int,sometext varchar(100) )
insert into #temp2(quote_id,sometext) values(1115529,’deleted’)
select * from sme_data.dbo.sme_quotes q inner join #temp2 m on m.quote_id = q.quote_id and q.preferredinsurer = m.sometext
end try
begin catch
select ERROR_NUMBER() AS ERRORNUMBER,ERROR_MESSAGE() AS ERRORMESSAGE,ERROR_SEVERITY() AS ERRORSEVERITY
end catch