onsdag 18 mars 2009

Stored procedure result into a table...

Needed to kill a large range of spid's today... but how can that be done in a simple and flexible way?

Well, put the result of sp_who2 into a table, do a select from that table to generate one column with the text 'kill '. And then copy paste the result and run it.

declare @sp_who table
(
spid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blkby char(5),
dbname nchar(128),
cmd nchar(16),
cputime int,
diskio int,
lastbatch varchar(20),
program nchar(128),
spid2 smallint,
requestid int
)

insert into @sp_who execute sp_who2

select * from @sp_who

Modify the final select to suit your needs.

Inga kommentarer:

Skicka en kommentar