SQL Server2008: BeginExecuteNonQuery / EndExecuteNonQuery Problem

Question

I have a stored procedure, which writes a backup of a specific database. I call this SP in a C# / Windows Forms application asynchrounously. Here is the code snipped:

IAsyncResult result = command.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
     System.Threading.Thread.Sleep(1000);
     ...
}
command.EndExecuteNonQuery(result));

After some time the program leaves the loop, because IsCompleted = true and calls EndExecuteNonQuery. The problem now is, that the Job is still busy and EndExecuteNonQuery is blocked! This causes a server timeout after some minutes. It seems that the IsCompleted value is not consistent respectively what's wrong with IsCompleted? How can I achieve that my program recognizes the "real job status"?

Answer 1

Make sure your stored procedure does not print anything and has no count reports (SET NOCOUNT ON). Async TDS calls call back at the very first packet sent by the server, but this packet might be a intermediate result (as in 1 row updated) and happen long, long before the actual completion. Does this suck? Yes. Can you do anything about it? No.

As a side note, is it infinitely more efficient to just pass a callback to the BeginExecute(...):

// Set form's state to 'executing', 
// eg. Button.Enabled = false; label.Text = 'Executing';
command.BeginExecuteNonQuery((asyncstate)=>
{
   try
   {
      command.EndExecuteNotQuery();
   }
   catch(SqlException ex)
   {
     ...
   } 
   // Reset the form's state to 'Ready'
   Invoke (()=>
   {
     // eg. Button.Enabled = true; label.Text = 'Ready';
   }
}