2

I have an asp.net app which might run long running queries. I would like the user to have the ability to cancel the query in SQL Server. I tried using BeginExecuteReader & EndExecuteReader using asynchronous operations with waithandles but all this does is cancel the thread after the waithandle's timeout is reached. The query is still running in SQL Server however. I can't use KILL command because this requires sysadmin or processadmin roles and the app's connection user is not allowed to have these elevated roles.

How can an ASP.NET cancel their query on demand? Just like hitting the stop button in SSMS but doing this using a button in a web page. Or signal SQL Server to stop the query as soon as possible without waiting for it to finish? (Using SQL Server 2012 & .NET 4.5)

UPDATE
I am adding this clarification. The user doesn't know upfront that the query will take a long time. For example they entered a huge date range by mistake, hit submit and go "oh oh!". They need to cancel now.

Tony_Henrich
  • 42,411
  • 75
  • 239
  • 374
  • See http://stackoverflow.com/a/889242/2382032 – Eric Scherrer Jul 15 '14 at 19:11
  • 2
    Did you try SqlCommand.Cancel? http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel.aspx . This has to be called from a separate thread. – Mez Jul 15 '14 at 19:11
  • 1
    and this http://stackoverflow.com/a/14052907/2382032 – Eric Scherrer Jul 15 '14 at 19:12
  • @Mez How is one thread getting a handle of SqlCommand's reference of another thread? I am assuming the cancel has to happen on the same sqlcommand that issued the query? – Tony_Henrich Jul 15 '14 at 19:17
  • according to docs: Cancel() = >" Tries to cancel the execution of a SqlCommand", meaning it is not guaranteed it can actually cancel. I suppose SQL Server can cancel procedures with many steps in it, and along a step it cancels. But for a query that has been submitted: I can't see how the SQL server could communicate with that. – Peter Aron Zentai Jul 15 '14 at 19:53
  • @PeterAronZentai So you think the documentation is just lying? Really you think .NET cannot communicate with SQL server. Then how would .NET get the results back if it cannot communicate. – paparazzo Jul 15 '14 at 20:04
  • @Eric While your links mention using cancel command, they don't say how to use it in a disconnected stateless environment like asp.net. – Tony_Henrich Jul 15 '14 at 23:02
  • @Blam IT is not lying. it says it tries to cancel. You cannot communicate with the sql server ALL the time. during an execution of a query I am pretty much sure you are not able to influence that query - since SQL server started processing and is not listening your commands. just when the query finished. – Peter Aron Zentai Jul 16 '14 at 07:03
  • @PeterAronZentai Yes you can communicate with sql server ALL the time. It is called threading. Clearly Cancel is a little late if Execute is blocking. That at does not mean Cancel does not working. – paparazzo Jul 16 '14 at 13:46
  • sure whatever http://stackoverflow.com/questions/889102/how-to-cancel-a-long-running-database-operation?lq=1 – Peter Aron Zentai Jul 16 '14 at 17:16

1 Answers1

0

This is a common issue at large enterprises that have sizable data tables. Can you you add a row limit to your SQL?

SET ROWCOUNT 4000;

You can then check your results in your app and if the rows returned hits the limit, you can send a message to the user.

Another solution is to issue a count() command prior to running the full query and identify the number of rows and bytes and determine if it is too much prior to running the full query.

Cancelling a query invokes a rollback at SQL Server. This is not something that can be stopped. So beyond cancelling the connection or command in ado.net that is not the ideal solution.

James Bailey
  • 199
  • 13