Canceling SQL Server query with CancellationToken

After looking at what your stored procedure is doing, it appears that it is somehow blocking the cancellation.

If you change

RAISERROR (@msg,0,1) WITH NOWAIT;

to remove the WITH NOWAIT clause, then the cancellation works as expected. However, this prevents the InfoMessage events from firing in real time.

You could track progress of the long running stored procedure some other way or register for the token cancellation and call cmd.Cancel() since you know that works.

One other thing to note, with .NET 4.5, you can just use Task.Run instead of instantiating a TaskFactory.

So here's a working solution:

private CancellationTokenSource cts;private async void TestSqlServerCancelSprocExecution(){    cts = new CancellationTokenSource();    try    {        await Task.Run(() =>        {            using (SqlConnection conn = new SqlConnection("connStr"))            {                conn.InfoMessage += conn_InfoMessage;                conn.FireInfoMessageEventOnUserErrors = true;                conn.Open();                var cmd = conn.CreateCommand();                cts.Token.Register(() => cmd.Cancel());                cmd.CommandType = CommandType.StoredProcedure;                cmd.CommandText = "dbo.[CancelSprocTest]";                cmd.ExecuteNonQuery();            }       });    }    catch (SqlException)    {        // sproc was cancelled    }}private void cancelButton_Click(object sender, EventArgs e){    cts.Cancel();}

In my testing of this, I had to wrap ExecuteNonQuery in a Task in order for cmd.Cancel() to work. If I used ExecuteNonQueryAsync, even without passing it a token, then the system would block on cmd.Cancel(). I'm not sure why that's the case, but wrapping the synchronous method in a Task provides a similar usage.