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

Generating Insert Scripts to Move Static Data Between Environments

You may create a codes table that defines an order type or a sales code, ect. You probably create the table in your local database and manually enter in the initial rows. When it is time to move to test, stage, and production, you don't want to manually key in the data. You have a couple options. You can write an SSIS job, or do an export to file and re-import the data in the new environment. This just seemed like more work that necessary. I thought it would be really nice if I could have them documented as insert statements, so the installer can simply run it when it is time to go to production.

To handle this scenario, I found an excellent post from Narayana Vyas Kondreddi. All you need to do is install his stored procedure in your master database and then you can generate insert statements by calling that stored procedure in the database or your choosing.

http://vyaskn.tripod.com/code.htm#inserts

Here are the two main type of execute statements that I found useful.

• Generating inserts for a table where you want all columns scripted:

EXEC sp_generate_inserts 'titles'

• Generating a table to include all columns except for the identity column:

EXEC sp_generate_inserts mytable, @ommit_identity = 1


Thanks Narayana for a great post!!!

Refreshing View and Recompiling Stored Procs

When working on a system that uses views that rely on other views or stored procs that rely on views/tables that may have changed, it is useful to refresh the views in the database to prevent binding errors. It is much easier to refresh all of your views and stored procs versus identifying which views and stored procs are dependent on your change.

Refreshing Views

The below code will automatically refresh all views in a given database. If your changes broken any of the binding then an error will be generated and the process will end. So, if you have broken multiple views you will need to fix a broken view, re-run the script, and check for errors.

DECLARE cursor_views CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='V'
FOR READ ONLY

OPEN cursor_views
DECLARE @name sysname

FETCH NEXT FROM cursor_views INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Refreshing view: '+@name
EXECUTE sp_refreshview @name
FETCH NEXT FROM cursor_views INTO @name
END

CLOSE cursor_views
DEALLOCATE cursor_views

Refreshing Views that have Errors or the SCHEMABINDING Option

You cannot refresh a view that has the schema binding attribute set. Your options are to either ignore the error or to remove the attribute and refresh the view.

The below code creates a script for you to run. It adds "GO" statements between each script so it will ignore errors. You can then look at the views that failed and determine wht

SET rowcount 0
DECLARE cursor_views CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='V'
FOR READ ONLY

OPEN cursor_views
DECLARE @name sysname

FETCH NEXT FROM cursor_views INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
Print 'sp_refreshview ' + @name
Print 'GO'
FETCH NEXT FROM cursor_views INTO @name
END

CLOSE cursor_views
DEALLOCATE cursor_views

After executing the script, this is the script it will create. Run this script and check for any errors.

sp_refreshview MYVIEW1
GO
sp_refreshview MYVIEW2
GO


If any views have WITH SCHEMABINDING set, you will receive this error message.
Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1
The object 'MYVIEW1' does not exist or is invalid for this operation.


Recompiling Stored Procs
Recompiling stored procs is done in a similar fashion. To recompile all stored procs you can run this script.

DECLARE cursor_procs CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='P'
FOR READ ONLY

OPEN cursor_procs
DECLARE @name sysname

FETCH NEXT FROM cursor_procs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Recompiling proc: '+@name
EXECUTE sp_recompile @name
FETCH NEXT FROM cursor_procs INTO @name
END

CLOSE cursor_procs
DEALLOCATE cursor_procs


It will return the follow message, which indicates that the stored proc will be recompiled the next time it is executed.

Note: Be sure to check the messages carefully to ensure that your changes did not break any bindings.

Recompiling proc: myProc1
Object 'myProc1' was successfully marked for recompilation.
Recompiling proc: myProc2
Object 'myProc2' was successfully marked for recompilation.