killing all processes in the database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

killing all processes in the database

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-02-11 : 00:43:01

hi,
I want to kill all the processes in the database to drop that specific database I am using the following script:

declare @spid int
declare kill_cursor cursor for (select spid from sysprocesses where dbid in (select dbid from sysdatabases where name='database_name'))
open kill_cursor
fetch next from kill_cursor into
@spid
while @@fetch_status =0
begin
kill @spid
print @spid
fetch next from kill_cursor
into @spid
end
close kill_cursor
deallocate kill_cursor
go

but it gives me error at the kill statement.
any ideas would be highly appreciated.

Regards,
Harshal.

Expect the UnExpected

srf
Starting Member

42 Posts

Posted - 2003-02-11 : 14:31:39

Looks like you need to wrap it in an exec. Example:

declare @SPID int
declare @sqlString varchar(255)
select @SPID = 54
select @sqlString = 'kill ' + convert(varchar, @SPID)
exec (@sqlString)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources