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.

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

 

Fix Error : Incorrect syntax near ‘*=’. In SQL Server (SQL JOINS)

Reason :

I have Migrated  one of my Database from SQL Server 2000 to SQL Server 2016.  And when I was running my applications after the migration of databases, I got some exceptions and then I looked what are the reasons for them..

The Error message was : 

Incorrect syntax near ‘*=’.

I have created a Sample Database with two tables to show you how it occurred and how to fix it.
Here I have wrote a sample quarry to show you how the error has occurred.


SELECT
ID, Name, Age,
CountryName, CountryCapital
FROM Customer cu,Country c
WHERE
cu.CountryID *= c.CountryID

This quarry works in SQL Server 2000 but, it gives and error in SQL Server 2016.

1-qry-sql-2000

Image 1 : LEFT JOIN in SQL SERVER 2000 using  “*=”

2-sql-2016-qry-error

Image 2 : LEFT JOIN in SQL SERVER 2016 using “*=”

Then I looked what is the reason and I got the reason for the error.

The reason is SQL Server 2016 does not support to write quarries with JOINS using ” =*” and “*=” .

But in SQL Server 2000, we can write quarry  with JOINS using  ” =*” and “*=” .

According to above quarry, there is a Left Join.

You can lean more about SQL JOINS using below reference..

w3schools.com/sql/sql_join

Solution :

  1. A one solution is we can change the compatibility level of the database in SQL Server 2016 using database properties.
  2. But the Solution I used is use  “LEFT JOIN” instead of  “*=” .

Then the error has fixed..

But for more information I’ll show you how we can write SQL JOINS in SQL SERVER 2000 and LATER VERSIONS of SQL SERVER 2005 to SQL SERVER 2016.

LEFT JOIN/LEFT OUTER JOIN

LEFT JOIN we can also call as LEFT OUTER JOIN. But both of them gives us the same result. These are use before the WHERE statement.

In SQL Server 2000, we can write LEFT JOIN as “*=”.  But this is using after the WHERE statement.

Then result shows us all records in the LEFT hand side tables and matching values in RIGHT hand side table.

3-using-left-outer

Image 3 : Difference ways to use LEFT JOIN in SQL SERVER 2000

4-using-left-outer

Image 4 : LEFT JOIN in SQL SERVER 2016

RIGHT JOIN/RIGHT OUTER JOIN

RIGHT JOIN  we can also call as the RIGHT OUTER JOIN. But both of them gives us the same result.

In SQL Server 2000, we can write RIGHT JOIN as “=*”.

Then result shows us all records in the RIGHT hand side tables and matching values in LEFT hand side table.

4-ridgt-outer-2000

Image 5 : Different ways to use  RIGHT JOIN in SQL SERVER 2000

5-right-outer-2016

Image 6 : RIGHT JOIN in SQL SERVER 2016

INNER JOIN

INNER JOIN shows only the matching values in both tables. This join we can use in both SQL SERVER version in the same way.

6-inner-join-2000

Image 7 : INNER JOIN in SQL SERVER 2000

7-inner-join-2016

Image 8 : INNER JOIN in SQL SERVER 2016

FULL JOIN/FULL OUTER JOIN

Using FULL JOIN it shows all matching and outmatching  values in both tables. It shows outmatching values as NULL.

FULL JOIN we can use in both SQL SERVERS in the same way.

8-full-join-2000

Image 9 : FULL JOIN in SQL SERVER 2000-2016

 

Thank You !

IF YOU WANT TO TEST THIS, PLEASE CREATE A DATABASE “TestDB”

OR run below quarry.


CREATE DATABASE [TestDB]

and then run this quarry. There and sample data to text your quarries.


USE [TestDB]
GO
/****** Object: Table [dbo].[Country] Script Date: 1/2/2017 6:18:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NULL,
[CountryCapital] [varchar](50) NULL
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Customer] Script Date: 1/2/2017 6:18:23 PM ******/
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 IDENTITY_INSERT [dbo].[Country] ON

INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (1, N'Sri Lanka', N'Sri Jayawardhanapura')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (2, N'India', N'New Dhilli')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (3, N'Beljium', N'Bruselles')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (4, N'United Kingdom', N'London')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (5, N'United States of America', N'Washington, D.C.')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (6, N'Japan', N'Tokyo')
SET IDENTITY_INSERT [dbo].[Country] OFF
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