CODEWITHSUNDEEP
sql-server
Krishna Kumar
Krishna Kumar

Reputation: 8201

Why do I get "The server encountered a stack overflow during compile time" error is SQL Server 2000 Sp4

I am trying to have around 6290 'AND' conditions in this query. I get the same for around 11945 'OR' conditions.

Exception details: The server encountered a stack overflow during compile time. at System.Data. lClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException excepti , Boolean breakConnection)

Upvotes: 0

Views: 1115

Answers (2)

gbn
gbn

Reputation: 432561

Try and optimise your AND/OR conditions.

SELECT * FROM foo
WHERE ([fooKey] = 1 AND Year = 1995)
OR ([fooKey] = 1 AND Year = 1996)
OR ([fooKey] = 1 AND Year = 1997)
OR ([fooKey] = 1 AND Year = 1998)
OR ([fooKey] = 1 AND Year = 1999)
OR ([fooKey] = 1 AND Year = 2000)
OR ([fooKey] = 1 AND Year = 2001)
OR ([fooKey] = 1 AND Year = 2002)
... ad infinitum

becomes

SELECT * FROM fooWHERE ([fooKey] = 1 AND Year between 1995 and 2002)
union
SELECT * FROM fooWHERE ([fooKey] = 10017 AND Year = 1995)
union
SELECT * FROM fooWHERE ([fooKey] = 10018 AND Year = 1997)
... slightly less

Or go to 64 bit and try and add enough memory for this not to happen...

Upvotes: 2

Jesse Pepper
Jesse Pepper

Reputation: 3243

Interesting bug! The obvious question to ask you is, why? The stack overflow is because recursion is the typical way to parse an SQL statement like that, which builds up a syntax tree. Depending on what is pushed onto the stack with each recursive call, it's not surprising. Did it hurt the server? ;)

Upvotes: 1

Related Questions