Why am I getting "Server encountered a stack overflow during compilation" - SQL Server 2000 Sp4
I am trying to find the order of 6290 'I' in this question. I get the same thing for 11945 OR conditions.
Exception Details: The server encountered a stack overflow during compilation. in System.Data. lClient.SqlConnection.OnError (SqlException, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError (SqlException excepti, Boolean breakConnection)
source to share
Interesting error! The obvious question you want to ask is why? Stack overflow is due to the fact that recursion is the typical way to parse a SQL syntax expression that creates a syntax tree. Depending on what is pushed onto the stack with each recursive call, this is not surprising. Did it damage the server?;)
source to share
Try and optimize 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 bits and try adding enough memory to prevent this from happening ...
source to share