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.

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

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..
Solution :
- A one solution is we can change the compatibility level of the database in SQL Server 2016 using database properties.
- 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.

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

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.

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

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.

Image 7 : INNER JOIN in SQL SERVER 2000

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.

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