This admin subsystem stored procedure is used to generate
a spid killing stored procedure for any database in a distributed
environment. Note that the server must be 'linked' to the admin
server.
CREATE procedure MakeSpidKiller
@pServerName varchar(128),
@pDbName varchar(128) -- include % for multiple likenamed dbs
as
declare @sProcedureName varchar(128)
declare @sSql nvarchar(4000)
set @sProcedureName = 'ExpungeUsers_' + @pServerName + '_' + @pDbName
if objectproperty(object_id(@sProcedureName), 'isProcedure') = 1
exec ('drop procedure ' + @sProcedureName)
-- begin generated procedure text
set @sSql ='create procedure ' + @sProcedureName + '
as
declare @spid int
declare @cmd varchar(100)
declare dbuserscursor cursor
for
select spid
from ' + @pServerName + '.master.dbo.sysprocesses p
inner join ' + @pServerName + '.master.dbo.sysdatabases d
on p.dbid = d.dbid
where d.name like ''' + @pDbName + '''
set nocount on
open dbuserscursor
fetch next from dbuserscursor into @spid
while (@@fetch_status = 0) begin
set @cmd = ''osql -Usa -E -S '
+ @pServerName
+ ' -dmaster -Q"kill '' + cast(@spid as varchar(5)) + ''"''
exec master.dbo.xp_cmdshell @cmd, no_output
fetch next from dbuserscursor into @spid
end
close dbuserscursor
deallocate dbuserscursor
if (select count(spid)
from '
+ @pServerName
+ '.master.dbo.sysprocesses p inner join '
+ @pServerName
+ '.master.dbo.sysdatabases d
on p.dbid = d.dbid
where d.name like ''' + @pDbName + ''') = 0
return
else
return 1'
-- end generated procedure text
exec sp_executesql @sSql
select 'Stored Procedure ' + @pServerName + '.' + db_name() + '.' +
user_name(objectproperty(object_id(@sProcedureName), 'ownerId')) + '.' +
@sProcedureName + ' successfully created'
