title: Async execution of a long-running stored procedure via a webservice
body: <p>I have a SQL Server stored procedure responsible for manipulating a large amount of data that takes anywhere from 15 seconds to several minutes to run. I am trying to execute it asynchronously using an async MVC action invoked by a request from a desktop UI client. I seem to be doing everything exactly the way .NET async books and tutorials teach, yet it seems the stored procedure gets aborted shortly after start.</p>
  
  <p>Upon start, the stored procedure adds a record to the status table to indicate a job in progress. Upon a successful completion, the stored procedure updates the status record with a success flag, and any errors are handled by a TRY-CATCH block that updates the status record with an error flag. </p>
  
  <p>This has been thoroughly tested in SQL Server Management Studio, so I am certain the stored procedure can't just bomb quietly. However, when the stored procedure is executed via the async call (see below), the job record is inserted into the status table, but it doesn't get updated, which means the stored procedure never reaches its end and is terminated mid-flight, possibly due to a closed database connection.</p>
  
  <p>The stored procedure call is wrapped in this async repository method:</p>
  
  <pre><code>    public async Task ProcessCatalogUpdateAsync(Guid updateID)
      {
          try
          {
              using (var cmd = new SqlCommand("ProcessCatalogUpdate", new SqlConnection(_connectionString)))
              {
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandTimeout = 0;
                  cmd.Parameters.Add("@UpdateID", SqlDbType.UniqueIdentifier).Value = updateID;
  
                  cmd.Connection.Open();        
  
                  await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
              }
          }
          catch (Exception ex)
          {
              var message = String.Format("Error processing catalog update ID={0}", updateID);
              throw new DataException(message, ex);
          }
      }
  </code></pre>
  
  <p>The controller action looks like this:</p>
  
  <pre><code>    public async Task UpdateCatalog(string updateID)
      {
          var uid = Guid.Empty; // breakpoint here
          if (!Guid.TryParse(updateID, out uid))
              throw new Exception("Bad UpdateID in request!");
  
          await _repository.ProcessCatalogUpdateAsync(uid);
  
          System.Diagnostics.Trace.WriteLine("Catalog update completed"); // breakpoint here
      }
  </code></pre>
  
  <p>I've built a test harness in the form of a simply HTML page that asynchronously invokes the action using jQuery like so:</p>
  
  <pre><code>    var requestURL = "/catalog/updateCatalog?updateID=" + $("#updateID").val();
  
      $.ajax({
          url: requestURL,
          type: "GET",
          error: function (xhr, status, errorText) {
              alert("Error: " + errorText); // breakpoint here
          },
          success: function () {
              alert("Call returned!"); // breakpoint here
          }
      });
  </code></pre>
  
  <p>I put breakpoints at both alerts in the JavaScript as well as at the beginning of my controller action method to confirm that the action is actually executed. I also have a breakpoint at the <code>System.Diagnostics</code> line immediately after the <code>await</code> call to the repository method to catch when the method returns (although it may be pointless in a debugging session as the thread context is likely to be different).</p>
  
  <p>This is what happens:</p>
  
  <ul>
  <li>The breakpoint at the beginning of the <code>UpdateCatalog</code> action is hit</li>
  <li>The stored procedure inserts a new row into the status table</li>
  <li>The <code>success</code> JavaScript handler never fires</li>
  <li>The status table is never updated with either a success or error flag</li>
  <li>The breakpoint at <code>System.Diagnostics</code> is never hit</li>
  </ul>
  
  <p>What am I doing wrong?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>As it turned out, there was a problem in the stored procedure code itself that at the very end was doing something that was taking over 20 minutes to complete. I have now rewritten it to work several orders of magnitude faster. However, the async stuff still isn't working the way I expect it. The jQuery AJAX <code>success</code> event fires only after the controller action method resumes after the <code>await</code> line instead of right away. </p>
  
  <p>How should I change my controller action method to execute in a true "fire-and-forget" way?</p>