Microsoft KB Archive/178443

= FIX: Regression Old Syntax Outer Join May Produce Error 613 =

Article ID: 178443

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q178443



BUG #: NT 17126(6.5)



SYMPTOMS
Using old style syntax, a query with an outer join gives good results, but on its next execution it causes an error 613, and all subsequent executions show the same behavior. This problem occurs if the following conditions are true:
 * The inner table has a minimum row length of 180 bytes.
 * The tables involved in the outer join have a minimum number of rows containing null values.
 * Table2 is the inner table.

SET SHOWPLAN ON, trace flag 302, and trace flag 310 do not give different results if the query runs correctly or fails with error 613. The following is the error message that is seen on the client, as well as on the SQL Server Errorlog:

Msg 613, Level 20, State 0

This problem occurs in SQL Server builds 6.50.240 and 6.50.258; the problem does not occur with SQL Server builds 6.50.201 and 6.50.213.



WORKAROUND
The script below shows the problem scenario and its two workarounds. Two different workarounds for this problem are provided after this script. set nocount on  go   drop table table1 go  create table table1 (cola int null) go  drop table table2 go  CREATE TABLE table2 (      col1 int NOT NULL,      --The datatype is not important;      --the NULL/NOT NULL property is important;      --the row length is important.      col2 char (176) NOT NULL   ) go

declare @cpt int select @cpt = 1 while (@cpt<=27) --Table1 needs at least 27 rows with a null value --in the column involved in the outer join. begin insert table1 select null select @cpt = @cpt+1 end insert table1 select 1 go

declare @cpt int select @cpt = 1 while (@cpt<=1961) --This is the minimum number of rows. begin insert table2 select 1, a select @cpt = @cpt+1 end

Workaround 1
Force a convert on the column type on for the inner table (in this case, table2), as in the following example: SELECT table1.col1, table2.cola FROM table2, table1 WHERE table1.cola *= convert(int,table2.col1)

Workaround 2
Use the ANSI style syntax, as in the following example: SELECT table1.col1, table2.cola FROM table2 RIGHT OUTER JOIN table1 ON table1.cola = table2.col1



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



MORE INFORMATION
It appears that the minimum number of rows on each table depends on the length of each row in the table description. The problem scenario given above gives the minimum length and the minimum number of rows for each table.

Keywords: kbbug kbfix KB178443

-

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

© Microsoft Corporation. All rights reserved.