可以在SQL Server查询被取消，真正/杀？ [英] Can sql server queries be really cancelled/killed?

问题描述

我想给用户取消运行的查询的能力。查询实在是太慢了。 （。查询优化是除了点），这主要是出于我的好奇心

I would like to give a user the ability to cancel a running query. The query is really slow. (Query optimization is besides the point.) This is mainly out of my curiosity.

MSDN说：

如果有什么要取消，没有发生。但是，如果在过程中
的命令，取消尝试失败，没有例外的是
生成

If there is nothing to cancel, nothing occurs. However, if there is a command in process, and the attempt to cancel fails, no exception is generated.

• Cmd的 - 的SqlCommand


• DA - DataAdapter的


• 连接 - 的SqlConnection


• CurrentSearch - 螺纹


• LongQuery - 辛格尔顿

• Cmd - SqlCommand
• DA - DataAdapter
• Conn - SqlConnection
• CurrentSearch - Thread
• LongQuery - Singleton

下面是我有：

var t = new Thread(AbortThread);
t.Start();

void AbortThread()
{
    LongQuery.Current.Cmd.Cancel();
    LongQuery.Current.Cmd.Dispose();
    LongQuery.Current.DA.Dispose();
    LongQuery.Current.Conn.Close();
    LongQuery.Current.Conn.Dispose();
    LongQuery.Current.Cmd = null;
    LongQuery.Current.DA = null;
    LongQuery.Current.Conn = null;
    CurrentSearch.Abort();
    CurrentSearch.Join();
    CurrentSearch = null;
}





我注意到 CurrentSearch.Abort（）挡住了，这就是为什么我在一个线程，这可能意味着该线程仍在工作包好。

I noticed that CurrentSearch.Abort() was blocking, that's why I wrapped it in a thread, which probably means that the thread is still working.

最后，还有什么比这一点，我可以做取消查询？它实际上可能取消从.NET这么长的查询？

Finally, is there anything else than this that I can do to cancel a query? Is it actually possible to cancel such a long query from .NET?

推荐答案

如果你真的绝对要杀掉它很好地利用这种方式：

IF you really absolutely want to kill it for good use this approach:



• 存储远权通过调用的 SELECT @@ SPID AS'SESSIONID' 在同一个连接



当你想杀死它：



• 打开一个新的数据库连接


• 问题一个 KILL 该会话ID命令
  
  当心为MSDN文档所需的权限 ALTER任何连接来做到这一点


• Open a new DB connection
• issue a KILL command for that session ID
  BEWARE as the MSDN documentation states you need the permission ALTER ANY CONNECTION to do this

这篇关于可以在SQL Server查询被取消，真正/杀？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！