Using Kill in a Cursor

[Randy]

I'm having trouble using the KILL command in a cursor.
My cursor below tries to KILL any process connected to databases TD% (Training
databases) before they are refreshed each night. I've tried using exec..
also without luck.
Any help would be appriciated.
************
declare spid_cursor cursor for
SELECT spid from master.dbo.sysprocesses join sysdatabases on sysprocesses.dbid=sysdatabases.dbid
where sysdatabases.name like ('TD%')

declare @spid smallint
open spid_cursor
fetch next from spid_cursor into @spid
while (@@fetch_status=0)
begin

kill @spid
fetch next from spid_cursor into @spid
end

close spid_cursor
deallocate spid_cursor

[DaveSatz]

I think you can achieve what you are tring to do (SQL Server 7+ or maybe 2K
only not sure) like:
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE

--

HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
-----------------------------------------------------------------

"Randy" <rdavis@baronbudd.com> wrote in message
news:3bf1db17$1@147.208.176.211...
>
> I'm having trouble using the KILL command in a cursor.
> My cursor below tries to KILL any process connected to databases TD%
(Training
> databases) before they are refreshed each night. I've tried using exec..
> also without luck.
> Any help would be appriciated.
> ************
> declare spid_cursor cursor for
> SELECT spid from master.dbo.sysprocesses join sysdatabases on
sysprocesses.dbid=sysdatabases.dbid
> where sysdatabases.name like ('TD%')
>
> declare @spid smallint
> open spid_cursor
> fetch next from spid_cursor into @spid
> while (@@fetch_status=0)
> begin
>
> kill @spid
> fetch next from spid_cursor into @spid
> end
>
> close spid_cursor
> deallocate spid_cursor
>
>

[Jeremy]

ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
The alter database will work but you will have to set in back to multi_user
if you want anyone other than your process to access once your done with
your process.

ALTER DATABASE databasename SET multi_user

You may also give an alloted time for user to leave the database before the
pids are killed maybe send a net send message that the db is going down and
then wait - 2 minutes later the serial killer kicks in on the pids

ALTER DATABASE databasename SET single_user WITH ROLLBACK AFTER 120 SECONDS

Hope this helps
Jeremy