Microsoft KB Archive/151513

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

Article ID: 151513

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q151513



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.'

Keywords: kbbug kbfix kbprogramming kbusage KB151513

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.