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;
}
}