BUG: SP2 Regr:Many-Column SELECT, JOINs, ORDER BY May Disconnect (195536)


The information in this article applies to:

  • Microsoft SQL Server 6.5 Service Pack 2 and later
This article was previously published under Q195536
BUG #: 18406 (SQLBUG_65)

SYMPTOMS

A query may disconnect the client process from the server if all of the following conditions are true:
  • The query returns many columns (having max rowsize over 2014).
  • There is more than one table involved in the query.
  • The query includes an ORDER BY clause.
When executed from within ISQL/W, the query may cause the ISQL/W tool to stop responding, forcing you to use the Windows NT Task Manager to close it. From within command line ISQL, the query will briefly stop the process then disconnect it.

In both instances, a handled exception access violation (AV) stack will be written to the SQL Server error log.

WORKAROUND

To work around this problem, do any one of the following:
  • Remove some of the columns.

    -or-
  • Alter the query, using UNION clauses or temporary work tables.

    -or-
  • For reporting sake, consider combining multiple result sets horizontally in the client-side (consequently, returning narrower result sets with each query).

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 2 and later.

MORE INFORMATION

This problem appears to be related to the issue described in the following article in the Microsoft Knowledge Base:

181923 : SELECT with Many Columns Causes Handled AV in Optimizer


But the stack trace generated is different, and in this instance there will be no mention of EXCEPTION_INT_DIVIDE_BY_ZERO, as in that case.

However, in light of the issue described in that article, it would be best to keep the total number of bytes-per-row returned less than 2,000.

Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbnofix KB195536
©2003 Microsoft Corporation. All rights reserved.