System.Data.SqlClient.SqlException: The query processor ran out of stack space during query optimization. Please simplify the query.

Every now and then we run into an error that comes out of the blue. The SQL update statement runs fine for years and one day they start throwing errors.

I had just such an issue this past week. Here is the error I received:

System.Data.SqlClient.SqlException: The query processor ran out of stack space during query optimization. Please simplify the query.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I am using an older ORM that dymanically creates an update statement. Here is a essentially what my update statement looked like:

UPDATE Customer
SET FirstName=@FirstName,
LastName=@LastName,
CustomerId=@CustomerId
VersionNumber=@VersionNumber + 1
WHERE CustomerId = @CustomerId
and VersionNumber = @VersionNumber

The bolded line is setting the primary key field, CustomerId, to the same value that exists in the database. When I pulled the line of code out of the update statement, the problem went away.

I've always known that updating the primary key in a record is a bad idea, but setting it to the same value seems pretty harmless. My guess is that a new update of SQL 2005 broke things.

Here is a KB article on the error: http://support.microsoft.com/default.aspx/kb/945896