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

• Microsoft SQL Server version 6.5

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 : kbprg kbusage SSrvProg kbbug6.50 kbbug6.00
Version : 6.5
Platform : winnt
Issue type :