FIX: Regression Old Syntax Outer Join May Produce Error 613

Retired KB Content Disclaimer

View products that this article applies to.

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.

Back to the top

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

Back to the top

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)

Back to the top

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

Back to the top

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 (http://support.microsoft.com/kb/197177/ ) INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Back to the top

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.

Back to the top

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

Back to the top

kbbug kbfix KB178443

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top