0

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

Community
  • 1
  • 1
Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
  • Hello, could you change the code of windows service? What is the service? What programming interface has it? – garik Mar 07 '10 at 09:15

1 Answers1

0

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();
garik
  • 5,669
  • 5
  • 30
  • 42
  • Igor, firstly, thank you for your time and comments. For 1) :- Right. I do have a table to track the status of the long running process.So, when the status is InProcess (user can see that as the UI is showing the status), he can cancel the operation. So this is exactly I was thinking (storing the processId). Windows service, just before it would initiate the insert/update, would store the process Id and UI would kill it, right? Is providing the permission for the kill process is just adding the Sql user to Admin role? Anybody has any experience in doing this for If this would work fine? – Ashish Gupta Mar 07 '10 at 16:46
  • it works. I used it 7 more years ago (sql server 2000). Now I prefer the case 2) – garik Mar 08 '10 at 12:36
  • Though this is not the answer which solved my problem. But close and appreciate the effort. Choosing this as answer. – Ashish Gupta Mar 23 '10 at 10:33