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



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 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]
CREATE TABLE [DemoCollation]
(DemoCollationNM VARCHAR(100))
CREATE TABLE #DemoCollation
(DemoCollationNM VARCHAR(100))

-- Insert records into both tables
INSERT dbo.DemoCollation
VALUES ('Test Join');
INSERT #DemoCollation
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.

collation conflict 01 SQL SERVER   Fix   Error: Msg 468, Level 16, State 9, Line 1

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 (http://blog.sqlauthority.com)

SQL SERVER – Creating Database with Different Collation on Server

multilang SQL SERVER   Creating Database with Different Collation on ServerI recently came across an organization who had very interesting infrastructure setup. Their business domains is analytics. They process millions of the records and how many clients who have case sensitive tags on their server which they want to measure. However, there are many places they do not want to care about case sensitivity. They had peta bytes of the data on their server. When I saw their servers – I noticed that every SQL Server had two instances installed – 1) Case Sensitive and 2) Case Insensitive. After a while I asked them why do they have two instances on the same server with almost similar data – one with case sensitive and another with case insensitive instances.

The answer I received was very interesting – they wanted both the data processed individually even though they are separate and in that they have two different instances on the same server. The organization was dealing with hundreds of the servers and instances, I could not digest that they have two similar instances. When I asked if they ever considered to create two separate database on the same instances instead of creating a new instance for just case sensitivity. The answer was something I did not expect at all. Here is what their Sr. DBA answered.

“Can we create a database with different collations on the same SQL Server Instance? We thought, the collation of the server is fixed during the installation and all the database on the system have to follow the same default collation.”

I took a deep breath and in their development database I ran following commands.

-- Create Case Sensitive Database
COLLATE SQL_Latin1_General_CP1_CS_AS
USE CaseSensitive
FROM sys.types
-- Create Case In-Sensitive Database
COLLATE SQL_Latin1_General_CP1_CI_AS
USE CaseInSensitive
FROM sys.types

The above command will create two different databases with Case-Sensitive and Case -Insensitive collations. When we retrieved the default datatypes from the database we can see that they have different collation for their data types. It is absolutely possible to database with a different collation on a same SQL Server Instance. It is also possible to create an individual column in a table with different collations from server instance and database as well.

collation is cs SQL SERVER   Creating Database with Different Collation on Server

Collation can be set at SQL Server instance Level, Database Level as well as Table Column Level. 

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)