Internal Query Processor Error: The Query Processor Ran Out Of Stack Space During Query Optimization

Posted January 30, 2007 at 12:34 PM

Tags: SQL

Wooohooo! I got a SQL server error that I have never seen before:

Internal Query Processor Error: The Query Processor Ran Out Of Stack Space During Query Optimization.

I am working on an old report for a client that originally had a small set of data and now has an ENOURMOUS set of data. Looks like it's time to start breaking a query up into smaller queries - give the SQL server less to worry about.

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page



Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

William from Lagos
Jan 30, 2007 at 12:59 PM // reply »
10 Comments
1 Points

Hi Ben,

Please keep us informed on your method of achieving this.

Thanks.


Michel de Ruiter
Feb 27, 2007 at 6:04 AM // reply »
1 Comments
1 Points

Is it a coincidence that the filename of this page starts with "500"? I find that I sometimes get this error when combining more than 500 fields in one expression in a SQL Server query.


David Levin
Oct 21, 2007 at 4:47 AM // reply »
3 Comments
1 Points

Microsoft offers the following solution for this problem: http://support.microsoft.com/kb/288095 however I would guess that there must be a better (and faster) way to get around this.


Erik
Jun 20, 2008 at 12:03 PM // reply »
1 Comments
1 Points

We received this error due to a bug that kept on appending items to a list that we used in an IN clause of a SQL Statement.
It blew up since the IN statement had 35,000 arguments.

Creating a temp table per the MS article is probably the best solution since you can throw a clustered index on the column.

This part of the MS article scared me a bit:
In some cases, SQL Server may actually shutdown as a result of the stack overflow.


Post Comment  |  Ask Ben

Recent Blog Comments
<scriptalert('just a test buddy :)')script
Nov 21, 2009 at 12:41 AM
Ask Ben: Building An AJAX, jQuery, And ColdFusion Powered Application
13311qseqweqwe ... read »
Budd Wright
Nov 20, 2009 at 11:32 PM
Five Months Without Hungarian Notation And I'm Loving It
I've used headless camel case for years for not only ColdFusion variables, but also SQL tables and fields... pretty much everything involving code. I also subscribe to the "don't abbreviate and clea ... read »
Ben Nadel
Nov 20, 2009 at 11:00 PM
Five Months Without Hungarian Notation And I'm Loving It
@Marcel, Yeah, I always err on the side of longer but more readable variable names. As for the camel casing of CF methods and the headless camel casing of custom items, I get around this by always ... read »
Marcel
Nov 20, 2009 at 10:56 PM
Five Months Without Hungarian Notation And I'm Loving It
I use the following and love it: my.namespace.MyComponents.functionMethodsOrUDF() CONSTANT_VALUES_OR_PROPERTIES One thing I always try is to CamelCaseBuiltInColdFusionFunctions() so others can tell ... read »
Darren
Nov 20, 2009 at 5:38 PM
Learning ColdFusion 8: CFImage Part I - Reading And Writing Images
Hi Ben, Great article. I've been looking around to see if ColdFusion image engine can programatically create the following "wrap around" effect: http://www.creativepro.com/article/photoshop-s-she ... read »
Todd Rafferty
Nov 20, 2009 at 5:35 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Dave: I talked to Gert he suggested: <cfhttp method="get" url="http://{some cf website}" result="stuff" addtoken="yes" /> Note the addition of cfhttp attribute addtoken. That should persist y ... read »
Ben Nadel
Nov 20, 2009 at 5:23 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Todd, Ahh, gotcha, yeah that makes sense. ... read »
Todd Rafferty
Nov 20, 2009 at 5:17 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
Ben, sorry if I didn't make this clear. You can make it work like that if you want, just put <cfset session.foo = 1> (and <cfset application.foo = 1>) in your OnRequestStart() and it reve ... read »