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:
- There are more than 5 tables involved in the query.
- 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