The query uses non-ANSI outer join operators.

Error Message:
Msg 4147, Level 15, State 1, Line 3
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 or lower, using stored procedure sp_dbcmptlevel. 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.

Severity level:

This error message appears when you try to use the old-style JOIN syntax against a database in SQL Server 2005 compatibility mode.

The SQL statement cannot be parsed and further execution is stopped.

Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. The old-style syntax must be converted into ANSI compliant JOIN syntax or the database must be run in compatibility mode less than 90 (SQL Server 2005).

This error message was introduced with SQL Server 2005.

  FROM Northwind.dbo.Orders o, Northwind.dbo.[Order Details] od
 WHERE o.OrderID *= od.OrderID

SQL Server 2005 still allows this syntax when the database is operated in a compatibility mode less than 90. Future versions of SQL Server however, are likely to support the syntax no more. Such statements such generally be rewritten to use the ANSI compliant JOIN syntax.


Leave a comment

Your email address will not be published.