FIX: ANSI Style Outer Join Can Fail w/ Error #301

 Last reviewed: April 8, 1997
Article ID: Q151513

The information in this article applies to:
  • Microsoft SQL Server, version 6.5
BUG#: 15265 (6.50)

SYMPTOMS

Using an ANSI style outer join can cause Msg 301, Level 16, State 1: 

   Query contains an illegal outer-join request.

CAUSE

You will get the error if the following conditions match:

  1. There are more than 5 tables involved in the query.
2. The outer table involved in these joins has more than 25 rows of data.

WORKAROUND

Disable 'join-order heuristic' with trace flag 320.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

SQL Server uses 'join-order heuristic' method to reduce the number of permutations in searching for the best join order, in the case with ANSI style outer join (i.e. LEFT OUTER JOIN, RIGHT OUTER JOIN, etc), one of the program variables is not set properly, hence if the above two conditions match, 'join-order heuristic' will fail every possible permutation and end up with Msg #301. With trace flag 310 turned on, you can observe every permutation is followed by 'IGNORING THIS PERMUTATION.'

Additional query words:
Keywords : kbbug6.00 kbbug6.50 kbprg kbusage SSrvProg
Version : 6.5
Platform : WINNT

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.