使用动态SQL杀死所有用户会话-exec中的exec [英] Kill all user sessions using dynamic SQL - exec within exec

问题描述

环境:SQL Server 2005/2008

Environment: SQL Server 2005/2008

我想使用动态SQL杀死特定数据库上的所有用户连接.我知道其他方法，例如将数据库模式更改为单次/离线等，但是想使用动态SQL.

I would like to kill all the user connections on a particular DB using dynamic SQL. I am aware of other methods like altering the DB mode to single/offline etc., but would like to do using dynamic SQL.

我试图避免在执行动态SQL时出现游标，while循环以及任何变量使用情况.这是我使用sysprocesses的代码:

I am trying to avoid cursor, while loop and also any usage of variable in executing the dynamic SQL. Here is my code using sysprocesses:

 
-- Construct KILL SQL
exec ('select ''kill '' + cast(spid as varchar(20)) + '';'' from sys.sysprocesses
    where spid NOT IN (@@spid)
    and spid > 50') 

现在，在构造了单独的kill语句之后，我想在不声明和使用如下变量的情况下执行.我是否缺少某些东西，或者exec中的exec不起作用?

After constructing individual kill statements now I would like to exec without declaring and using a variable as below. Am I missing something or that an exec within exec doesn't work?

  
-- execute KILL SQL
exec ('exec (''select ''''kill '''' + cast(spid as varchar(20)) + '''';'''' from sys.sysprocesses
where spid NOT IN (@@spid)
and spid > 50'')')

我知道一种更好的声明变量的方法，如下所述，但是我希望上面的方法可以在不使用变量的情况下工作:

I know a better way to declare a variable and do it as below but I would like above to work if it is possible without using a variable:

 
-- routine way of doing KILL
declare @dsql nvarchar(max)=''
select @dsql = 'kill ' + cast(spid as varchar(20)) + ';' from sys.sysprocesses
where spid NOT IN (@@spid)
and spid > 50
exec(@dsql)

推荐答案

我建议您使用最新版本是最佳解决方案.

I'd suggest that your last version is the best solution.

这篇关于使用动态SQL杀死所有用户会话-exec中的exec的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！