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)
I am using an older ORM that dymanically creates an update statement. Here is a essentially what my update statement looked like:
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