3

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Time Machine
  • 157
  • 1
  • 14
  • you can specify a `CommandTimeout` if you want to cancel this query when it runs over a specified time limit. – StepUp Feb 29 '16 at 21:31
  • you can send the `kill` command down the wire if you know your process ID. If you do not know your process ID you can find it with `sp_who` – gh9 Feb 29 '16 at 21:31
  • @StepUp that is wrong, the `CommandTimeout` will not effect a currently running command. CommandTimeout has no effect when the command is executed against a context connection (a SqlConnection opened with "context connection=true" in the connection string). from the msdn page https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx – gh9 Feb 29 '16 at 21:33
  • 1
    May be this [answer](http://stackoverflow.com/a/3699391/881798) will give you hint. This is only for Application side connection termination. It will not actually kill the resource blocking execution on Sql server instance. – vendettamit Feb 29 '16 at 21:34
  • @Stepup Thanks, but `CommandTimeout` is not an option for me as some of these SPs are expected to take that long. I need a proper way to cancel them if necessary. – Time Machine Feb 29 '16 at 21:34
  • 1
    Consider the Async methods? E.g. https://msdn.microsoft.com/en-us/library/7b6f9k7k(v=vs.110).aspx – Dan Field Feb 29 '16 at 21:37
  • @gh9 I need this to be done from the front end, Ideally users will cancel their own long running SQL command... – Time Machine Feb 29 '16 at 21:38
  • @TimeMachine. You can display all running process in an html table and let them click a button next to the process they want to kill, then send the `kill` command down the wire – gh9 Feb 29 '16 at 21:39
  • @DanField thanks, how would you send the cancellation to SQL with Async methods? any examples? – Time Machine Feb 29 '16 at 21:47
  • 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. – usr Feb 29 '16 at 22:59
  • Do you need to actually have the proc abort immediately or would it be enough to call Cancel and then just pretend that it's cancelled and continue execution? If this is a UI issue you could simulate to the user that it's cancelled. – usr Feb 29 '16 at 23:01

2 Answers2

2

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.

usr
  • 168,620
  • 35
  • 240
  • 369
-2

you can put all the code of getting this large data on Thread ,

and you count time or you give the user a button to cancel the data acquisition,

if certent time did pass or the user press cancel button the process will be canceled

1) declare a global variable 

Thread tr;

then when thread start start a timer and when the setted time on timer is elapsed he will call elapsed... Method i don't remember what's the exact name of the procedure search for who to use timer on c# on this Elapsed method add this line 

tr.Abort(); //Aborting the thread of course this will cancel all the code inside the thread executed method.

if else you don't wan't to use timer add a button and in it's click eventhandler method add the same line: 

tr.Abort();

this will cancel the executed code inside the thread method .

Bilal
  • 1,254
  • 13
  • 14
  • Thanks Bilal, but not sure whether using `Abort()` method is the safest way of terminating a procedure? – Time Machine Feb 29 '16 at 22:18
  • Abort will not terminate anything SQL Server side. Also, Google for "thread.abort evil" to see that it can never be used for cancellation. – usr Feb 29 '16 at 23:00
  • What usr says is correct. Thread.Abort also generally doesn't work the way most developers believe. Please refer to the documentation to see how it works internally. – Alex Maker Apr 14 '17 at 00:08