Mar 18, 2014

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Suppose we have created two tables and inserted some data using following sql query:

CREATE TABLE Customer(
    CustID INT PRIMARY KEY IDENTITY,
    Emp_Name VARCHAR(100)
)

INSERT Customer VALUES('Scott'),('Greg'),('Davis')

CREATE TABLE Cust_Detail(
    DetailID INT PRIMARY KEY IDENTITY,
    CustID INT REFERENCES Customer,
    Age INT,
    City VARCHAR(20)
)

INSERT Cust_Detail VALUES(1,20,'LA'),(2,23,'WC')

Now if we will execute following sql query:

SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c,Cust_Detail d
WHERE c.CustID *= d.CustID

Or

SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c,Cust_Detail d
WHERE c.CustID =* d.CustID

We may get error message like this:

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Cause: Newer version of sql server doesn't support outer join operators ("*=" or "=*")

Solution:

We can resolve this problem it two ways:

Solution 1: Use equivalent sql statement of newer version. That is:

SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c LEFT OUTER JOIN Cust_Detail d
ON c.CustID = d.CustID

Or

SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c RIGHT OUTER JOIN Cust_Detail d
ON c.CustID = d.CustID

Solution 2: Set the compatibility level for current database to 80

EXEC sp_dbcmptlevel YourDbName,80
GO
SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c,Cust_Detail d
WHERE c.CustID *= d.CustID

Or

SELECT
    c.CustID,
    c.Emp_Name,
    d.Age,
    d.City
FROM Customer c,Cust_Detail d
WHERE c.CustID =* d.CustID

No comments:

Post a Comment