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.

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”.

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.

Then lets change the collation of the 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…

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 

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