Terminate a long running SQL command

Question

Let's assume that our front-end app (C#) makes a series of connections to SQL Server and executes a series of stored procedures which may take few hours to complete. I need to have a facility to 'Cancel' a running stored procedure. I know that this can be achieved by Sqlcommand.Cancel(), but this does not seem to be effective at all times. Is there a better way to achieve this?

Answer 1

If cancel doesn't work, killing probably doesn't either. They must be using the same mechanism. Makes no sense to have different mechanism for them internally.

I need this to be done from the front end, Ideally users will cancel their own long running SQL command

It is a common technique to simulate to the user that an operation is cancelled although the operation is still finishing in the background. In .NET there is a variety of operations that cannot be cancelled immediately. In that case the best option is to isolate that action and let it finish in the background.

The TPL makes this fairly easy. You can search for ".net cancel task" or ".net cancel uncancellable task" to get some pointers. Again, the idea is to just continue execution and only request cancellation, not wait for it.

You don't need to use async/await or async SqlCommand methods to make this work but you can. Just mentioning this because that is a common misconception.