Microsoft KB Archive/288095

= Stack overflow occurs when you run a query that contains a large number of arguments inside an IN or a NOT IN clause in SQL Server =

Article ID: 288095

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q288095



BUG #: 235727 (shiloh_bugs)

BUG #: 58274 (sqlbug_70)



SYMPTOMS
Queries that contain a large number of arguments (thousands) inside an IN or a NOT IN clause may generate a stack overflow. For example, the following query results in a stack overflow: SELECT max(au_id) FROM authors WHERE au_id IN(1,2,5,......,11571) -- Query contains over 11570 arguments. The SQL Server error log contains information similar to the following when the stack overflow occurs: 2000-08-10 12:02:37.87 spid51   08/10/00 12:02:37 Stack Overflow Dump not possible - Exception c00000fd E at 0x00587286 2000-08-10 12:02:37.87 spid51   Address=587286 Exception Code = c00000fd 2000-08-10 12:02:37.87 spid51   eax=195922d0 ebx=19592338 ecx=2ad0e938 edx=00000007 2000-08-10 12:02:37.87 spid51   esi=196ce2c8 edi=19592180 eip=00587286 esp=2ac93000 2000-08-10 12:02:37.87 spid51   ebp=2ac93028 efl=00010202 2000-08-10 12:02:37.87 spid51   cs=1b ss=23 ds=23 es=23 fs=38 gs=0 2000-08-10 12:02:37.87 spid51   1: Return Address 00587286 2000-08-10 12:02:37.87 spid51   2: Return Address 0058728B................. In some cases, SQL Server may actually shutdown as a result of the stack overflow.



WORKAROUND
Rewrite the query and use a #temp table to contain the values in the IN list instead of using an IN clause. For example, the preceding query can be re-written like this: CREATE TABLE #IN_values (au_id char(4)) INSERT INTO #IN_values select au_id FROM Table_with_values_123456789...

SELECT max(au_id) FROM authors as A JOIN #IN_values as I ON (A.au_id = I.au_id)



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The client is not disconnected but this error message may occur:

Server: Msg 8621, Level 17, State 1, Line 2 Internal Query Processor Error: The query processor ran out of stack space during query optimization.

Keywords: kbprb kbpending KB288095

-

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

© Microsoft Corporation. All rights reserved.