Outer Join Issues in Sql 2005

Hi,

The following query works fine in Sql 2000 database, but it throws an error ('Query contains an outer-join request that is not permitted.') while executing the query on Sql 2005 database. We have already set the compatibility level of the Sql 2005 database to 'Sql Server 2000(80)'. But still we are getting the error.

select top 2 *
from table1 t1, table2 t2
where t2.col8 =* t1.col1
and t2.col9 =* ( select max(t21.col9) from table2 t21
where t21.col8 =* t2.col8 )

We are planning to convert all our sql 2000 databases to sql 2005 databases. We have used this kind of outer join(=* or *=) in many places in our application. It is also working fine many places in 2005 database, but throw errors in some places only.

Please let me know if any one knows the reason for the error in some places alone. So that we can give priority to those kind of query and change the outer join to 'SQL-92 compliant'. We know that this kind of outer joins are NOT going to be supported in future version of Sql and we are going to change all such outer join. But now we do not have much time to change all such outer join. It will be a great help if any one have answer for this.

Thanks in advance,

Sankar N

Is this error related only to the statements including an outer join on a subquery?

Regards

|||

You will have to modify the query to use the OUTER JOIN syntax. There is no way to make it work in SQL2005 for all cases. The compatibility level doesn't help in this case due to changes in query plan generation in SQL2005 (there are lot of changes, some of the syntax evaluation is more strict, new optimizations etc). The old proprietary *= and =* syntax has lot of ambiguities and it can produce incorrect results. It is best to convert all statements with old syntax or do it on a case-by-case basis which might be problematic.

sql