Microsoft KB Archive/195536

= BUG: SP2 Regr:Many-Column SELECT, JOINs, ORDER BY May Disconnect =

Article ID: 195536

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 2

-



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.

Additional query words: AV maximum freeze hang hangs hung errorlog err worktable worktables

Keywords: kbbug kbnofix KB195536

-

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

© Microsoft Corporation. All rights reserved.