SQL SERVER – Fix – Error: Msg 468, Level 16, State 9, Line 1

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')

Solarwinds

 

——————————————————————–

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)

Solarwinds
Previous Post
Interview Question of the Week #028 – Few SQL Best Practices
Next Post
SQL SERVER – FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

Related Posts

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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Paul Godfrey
    July 20, 2015 1:18 pm

    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?

    Reply
  • 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

    Reply
    • 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)

      Reply
      • 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

      Reply
  • Thank you Pinal interesting point

    Reply
  • Thank you fr the post, interesting for me.

    Reply
  • Thanks for this information Sir Pinal

    Reply
  • Patrick Braekevelt
    September 17, 2019 3:46 pm

    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

    Reply

Leave a Reply

Menu