KILL command?killing processes to go single user mode

[Grant007]

Hi im trying to write a store procedure to kill off processes on a certain database before i put it into singer user mode to restore a DB.

Lookint at it the KILL command cannot be used inside a store procedure? So does anyone know how to do this a different way or use a different command?

any help would be greatly appreciated...!

 

[hmckillop]

you can execute it from an osql prompt.
so create a text file and put in it the commands you need i.e. kill 51 etc
Have a look at "osql" in BOL.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[RobertT687]

It can be used if you build Dynamic SQL and execute it in a stored procedure.
We use the following to kill connections to our reporting databases where the user fails to log out after more than 60 minutes.
CREATE Procedure usp_Kill_Idle_SPIDS
AS
Declare @killspid smallint
Declare @killCmd varchar(255)

Declare spid_cursor Cursor For
Select spid
From master.dbo.sysprocesses
Where status = 'sleeping'
And loginame = 'myloginname'
And db_name(dbid) IN ('mydb1','mydb2','mydb3')
And Datediff(mi, login_time,getdate()) > 60
--
OPEN spid_cursor
Fetch Next From spid_cursor Into @killspid
--
WHILE @@FETCH_STATUS = 0
BEGIN
Select @killCmd = 'KILL ' + Cast(@killspid As varchar(3))
EXEC (@killCmd)
Fetch Next From spid_cursor Into @killspid
END
CLOSE spid_cursor
DEALLOCATE spid_cursor

GO

The procedure is creaded in master and runs as a scheduled job.

 

[donutman]

OMG, a valid use of dynamic SQL and a cursor to boot (hehe - pun intended)!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[RobertT687]

Now if I could just work in a trigger and a UDF it would be perfect, right?

 

[donutman]

I guess any two or all four wrongs make a right?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]