Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity 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, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity 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, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--