How to cancel a long running database insert/update operation from ASP.NET UI when the the operation is being oerformed by a Windows service

Question

I see a question about this here. However, in my case, It is the windows service which in running the long running insert/update on a table and I need to cancel the operation from my ASP.NET application. In the above link Burnsys suggests that one should kill the Sql server session. Is that really only way and a goood practice to do this?

Also, in the same poset somebody suggests one can use SqlCommand.Cancel to cancel. However, I am not sure how can I cancel the command from the windows service from ASP.NET application.

Please advice.

Thanks in advance for all your valuable comments, Ashish

Answer 1

1) If you can change the code of windows service or stored proc, I suggest creating some table on sql server to store states of long running task. Windows service (than could be executed by service) would add records to this table. ASP.NET application would monitor this table (manualy) to stop any runnning process.

If you still using sql server 2005, you still can use KillProcess(ProcessId). So store processId to this table and asp.net application can send kill process command to sql server (if you have permissions).

2) If you have any programming (wcf for example) interface of service that hosted in windows service, so I suggest exetuting LR task asynchronously and add method to cancel this task. ASP.NET application could call this cancel method.

SqlCommand command = new SqlCommand();
            command.BeginExecuteNonQuery();

...
            command.Cancel();