Friday, November 7, 2008

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

Tuesday, November 4, 2008

Checking for Multiple Instances of a Windows Forms or Console Application

When running a windows application or console/batch application you may run into issues if you have multiple instances running on the same machine. The applications may cause contention when reading and writing to a file system or database. Depending on how you design your application, it may cause data loss.

To handle this, you can use the System.Diagnostics namespace to check for existing instances of the application. If an instance exists with a different process ID, kill the existing process.

Note: You can kill the existing instance instead using process.Kill();

// Grab the current process so you can pull it's name
Process currentProcess = Process.GetCurrentProcess();
// Get existing processes on the current machine with the same name
Process[] processes = Process.GetProcessesByName(currentProcess.ProcessName);
foreach (Process process in processes)
{
// Loop through and check for any instance with the same name
if (process.Id != currentProcess.Id)
{
MessageBox.Show("Application is already running");
Application.Exit();
return;
}
}

// This piece of code isn't necessarily required. When using Visual Studio, your windows
// app runs under [ApplicationNam].vshost. This checks for these processes as well.
processes = Process.GetProcessesByName(currentProcess.ProcessName.Replace(".vshost", ""));
foreach (Process process in processes)
{
if (process.Id != currentProcess.Id)
{
MessageBox.Show("Application is already running");
Application.Exit();
return;
}
}

Wednesday, October 15, 2008

Page does not contain a definition for 'Context'

I inherited a website this week and it wouln't build. In the past the website was built in debug mode and the files were moved out manually. It wouldn't built in release mode and it wouldn't allow for me to do a publish website. It received the error ... "does not contain a definition for context."

The web page had the following page definition:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyPage1.aspx.cs" Inherits="MyPage1" %>

Whereas, the codebehind for MyPage1 (MyPage1.aspx.cs) included the wrong class name. I updated the class name to correspond to the class name mentioned in the web page and it will now build in release mode.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class MyPage2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
}

Sunday, October 5, 2008

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!!!

Impact Analysis for Database Changes

When you update a stored proc you may wonder if the stored proc is being called by another stored proc, or if you are updating a view you may wonder if your view is referenced by another view. In SQL Server Management Studio, they have greatly improved the ability to do impact analysis. Simply right click on the view/table/stored proc and click "View Dependencies." You can determine which views depend on this object and what objects this object depends on.





Second Check
Another option is to run a quick query to see if a field name or view/table name is referenced within a view definition or stored proc definition within the syscomments table. This isn't as reliable as the built-in functionality, but it serves as a second check.

select distinct name from syscomments a
inner join dbo.sysobjects b on a.id = b.id
where text like '%orderdescription%'

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.