Tag Archives: Cannot resolve the collation conflict

Fix Error : Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

Posted on by
Reply

Reason

When I was running a quarry with a JOIN, I got an error which says “Cannot resolve the collation conflict“

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

0-error

Image 1 : Error

Then I looked what is the reason for the error and I got the reason is Collations are not matching in tables that I have joined.

So here I’ll show you that How was the error occurred and I I did solved the error…

First I’ll create two tables “Customer” and “Person”.

1-tables

Image 2 : Sample Tables Customer/Person

Now I’m going to JOIN these two tables by CustomerName of customer table and CustomerName of Person table.

Before that, I’ll add different collations to the name fields in both tables.

First will change the collation of “Person” table..

Go the the designer of the table and select “Collation” menu. Then we can see a dialague box to select a Collation.

2-cp1

Image 3 : How to change the collation of a column

3-select-collation

Image 4 : Select SQL_Lathin1_General_CP1_CI_AI for CustomerName in Person Table

Then lets change the collation of the CustomerName in Customer table..

4-cp-1250

Image 5 : Select SQL_Lathin1_General_CP1250_CI_AS for CustomerName in Customer table

Yes. Now we have done the changes to both tables. So Now we are ready to run the quarry with the JOIN…

5-error

Image 6 : Error happen when run the quarry

Oh ! It gives an error …

What is the difference.. ?

SQL_Lathin1_General is common for both collation types.

CI means Case Insensitive. (we can use CS to make it case sensitive)

AI means Accent Insensitive and AS means Accent Sensitive

Then the next difference is CP1 and CP1250..

What is CP1 and CP1250 ?

Both of CP1 and CP1250 are code page architectures..

According to Microsoft technical page , CP1 is code page 1252 and CP1250 is code page 1250 which are…Rules to define how the bit pattern of the characters are sorted and compared. 

Learn more about code Pages @technet.microsoft.com…

So Lets Solve the error..

What we have to do is add Collation as “DATABASE_DEFAULT” for both table in the JOIN. Below image describes how to do it..


SELECT * FROM [dbo].[Customer] c
INNER JOIN [dbo].[Person] p
ON c.[Name] COLLATE DATABASE_DEFAULT =p.[CustomerName] COLLATE DATABASE_DEFAULT
6-result

Image 7 : Solve the error using DATABASE_DEFAULT

Now the error is not there and the quarry gives the result successfully.

Thanks !

To Test the result, Create a Database “TestDB” and please use below script to create a the tables and get sample data that I have added.


USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (1, N'Buddhima', 24, 1)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (8, N'Chrystophe', 45, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (9, N'Patel', 30, 2)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (10, N'Nikolas', 20, 3)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (11, N'Ann', 35, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (12, N'Mike', 50, 4)
SET IDENTITY_INSERT [dbo].[Customer] OFF
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (1, N'Buddhima')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (2, N'Ann')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (3, N'Mike')
SET IDENTITY_INSERT [dbo].[Person] OFF

 

Advertisements