Illegal use of outer join operator.

Applies to: SQL Server 2012, SQL Server 2014.
 

Problem Description.
 

After upgrading from SQL Server 2005 to SQL Server 2012 you may find that queries using outer join operators like *= and =* fail.

If you try to run those queries on SQL Server Management Studio 2012 you may receive the following error:


Illegal use of outer join operator.
The text that you have selected cannot be represented graphically using the Query Designer. Please select just the text necessary to design a SELECT, DELETE, INSERT or UPDATE statement.


Solution.


Non-ANSI outer join operators like *= and =* are not supported in SQL Server 2012 as mentioned on this article:

These types of outer join operators were deprecated since SQL Server 2008 as mentioned on this article.

These outer join operators were supported on SQL Server 2005 for databases having compatibility level 80, but since SQL Server 2012 does not support compatibility level 80 anymore you are forced to rewrite Transact-SQL queries using those operators and replace them by the LEFT OUTER JOIN and RIGHT OUTER JOIN keywords. Trying to set the compatibility level of a database to 80 using T-SQL on SQL Server 2012 may result on the following error:

Msg 15048, Level 16, State 1
Valid values of the database compatibility level are 90, 100, or 110.

SQL Server Upgrade Advisor is able to detect not supported keywords and operators like these non-ANSI operators as explained here.


 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.