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

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.

Image 3 : How to change the collation of a column

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

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…

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

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