Kill Session in Different SQL Server

Question

I have made SQL Query to kill all blocking sessions

declare @tbl table (sesid int)
insert into @tbl 
select BlkBy from (SELECT  spid,sp.[status],loginame [Login],hostname, blocked BlkBy,sd.name DBName, 
cmd Command,cpu CPUTime,physical_io DiskIO,last_batch LastBatch,[program_name] ProgramName   
FROM master.dbo.sysprocesses sp JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid ) as tbl
where BlkBy <> 0
DECLARE @n int, @i int= 0, @s int, @killCommand nvarchar(20)= 'kill ', @sql nvarchar (255)
SELECT @n = COUNT(*) FROM @tbl
WHILE @i < @n
BEGIN 
    SELECT TOP 1 @s = sesid from @tbl
    SET @sql = @killCommand + cast(@s as nvarchar(10))
    EXECUTE sp_executesql @sql
    delete from @tbl where sesid = @s
    SET @i = @i + 1
END

Now I'm working on a WEB Application to control more than 1 SQL Server that all these servers are linked to the control application database, I need to call the final Kill command from another SQL Linked server, so how to kill the session from one server to another linked server?

Answer 1

Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]
[ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
)
[ AS { LOGIN | USER } = ' name ' ]
[ AT linked_server_name ]
[ AT DATA_SOURCE data_source_name ]
[;]

MS Docs EXECUTE command