If you are upgrading to SQL Server 2005, pay attention to this one …
SQL Server has long supported two forms of OUTER JOIN syntax, the ANSI syntax (using LEFT OUTER JOIN, RIGHT OUTER JOIN, etc.), and the simplified T-SQL syntax (using *= and =*). If you’ve always used ANSI syntax then you are safe, but if you have any existing code that uses the simplified T-SQL syntax, that code will not run on SQL Server 2005, and the following error message will be returned:
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.
Fortunately, that message also provides the solution. Using the sp_dbcmptlevel stored procedure you set the backwards compatibility level so that the old style outer joins work. Until you manually fix them all, that is.
Leave a Reply