How to Kill Session Using Tsql
Hello I Have Problem with Cast Issue. Even the Results Come from Query as String I Get Msg 102, Level 15, State 1, Line 21 Incorrect Syntax Near '@Session_Id'...
Hello I have problem with cast issue . Even the results come from query as string i get Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '@session_id'. then I tried to use cast method but it did not worked .
declare @counter int;
declare @session_id int;
set @counter=0;
select
@counter= count(*),
@session_id=cast(req.session_id as int) from sys.dm_exec_requests req where req.command='DbccSpaceReclaim'group by req.session_id
if(@counter>0)
begin
kill @session_id
end
3 Answers
Try the following, I am using sp_executesql. For more information see
declare @counter int;
declare @session_id int;
set @counter=0;
select
@counter= count(*),
@session_id=cast(req.session_id as int) from sys.dm_exec_requests req where req.command='DbccSpaceReclaim'group by req.session_id
if(@counter>0)
begin
declare @sql nvarchar(1000)
select @sql = 'kill ' + cast(@session_id as varchar(50))
exec sp_executesql @sql
end
In my case it does not work (SQL Server 2008 R2)
DECLARE @sSQL varchar(MAX)
SET @sSQL = 'KILL 58'
EXEC sp_executesql @sSQL
It does not kill the session
KILL 58
This works
You can't use a variable as a parameter to the kill command directly.
Try creating a small piece of dynamic SQL and then executing it, as follows:
DECLARE @sql NVARCHAR(50)
IF(@counter>0)
BEGIN
SET @sql = 'kill ' + CAST(@session_id AS NVARCHAR)
EXEC @sql
END
This achieves the same result as you desire by executing the kill command with the value of @session_id.
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
