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.

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.

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.

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.

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.

 

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