title: Canceling SQL Server query with CancellationToken
body: <p>I have a long-running stored procedure in SQL Server that my users need to be able to cancel.  I have written a small test app as follows that demonstrates that the <code>SqlCommand.Cancel()</code> method works quite nicely:</p>
  
  <pre><code>    private SqlCommand cmd;
      private void TestSqlServerCancelSprocExecution()
      {
          TaskFactory f = new TaskFactory();
          f.StartNew(() =&gt;
              {
                using (SqlConnection conn = new SqlConnection("connStr"))
                {
                  conn.InfoMessage += conn_InfoMessage;
                  conn.FireInfoMessageEventOnUserErrors = true;
                  conn.Open();
  
                  cmd = conn.CreateCommand();
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandText = "dbo.[CancelSprocTest]";
                  cmd.ExecuteNonQuery();
                }
             });
      }
  
      private void cancelButton_Click(object sender, EventArgs e)
      {
          if (cmd != null)
          {
              cmd.Cancel();
          }
      }
  </code></pre>
  
  <p>Upon calling <code>cmd.Cancel()</code>, I can verify that the underlying stored procedure stops executing essentially immediately.  Given that I use the async/await pattern quite heavily in my application, I was hoping that the async methods on <code>SqlCommand</code> that take <code>CancellationToken</code> parameters would work equally well.  Unfortunately, I found that calling <code>Cancel()</code> on the <code>CancellationToken</code> caused the <code>InfoMessage</code> event handler to no longer be called, but the underlying stored procedure continued to execute.  My test code for the async version follows:</p>
  
  <pre><code>    private SqlCommand cmd;
      private CancellationTokenSource cts;
      private async void TestSqlServerCancelSprocExecution()
      {
          cts = new CancellationTokenSource();
          using (SqlConnection conn = new SqlConnection("connStr"))
          {
              conn.InfoMessage += conn_InfoMessage;
              conn.FireInfoMessageEventOnUserErrors = true;
              conn.Open();
  
              cmd = conn.CreateCommand();
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandText = "dbo.[CancelSprocTest]";
              await cmd.ExecuteNonQueryAsync(cts.Token);
          }
      }
  
      private void cancelButton_Click(object sender, EventArgs e)
      {
          cts.Cancel();
      }
  </code></pre>
  
  <p>Am I missing something in how the <code>CancellationToken</code> is supposed to work?  I'm on .NET 4.5.1 and SQL Server 2012 in case it matters.</p>
  
  <p>EDIT: I rewrote the test app as a console app in case the synchronization context was a factor and I see the same behavior -- the invocation of <code>CancellationTokenSource.Cancel()</code> does not stop the execution of the underlying stored procedure.</p>
  
  <p>EDIT: Here's the body of the stored procedure I'm calling in case that matters.  It inserts records and prints results at one-second intervals to make it easy to see whether cancellation attempts took effect promptly.</p>
  
  <pre><code>WHILE (@loop &lt;= 40)
  BEGIN
  
    DECLARE @msg AS VARCHAR(80) = 'Iteration ' + CONVERT(VARCHAR(15), @loop);
    RAISERROR (@msg,0,1) WITH NOWAIT;
    INSERT INTO foo VALUES (@loop);
    WAITFOR DELAY '00:00:01.01';
  
    SET @loop = @loop+1;
  END;
  </code></pre>