SQL Server2000中如何刪除指定的sleeping狀態的程式連線

Windows 2000

SQL Server 2000 SP4 

 

CREATE     PROCEDURE    dbo.KillProcesses @dbName varchar(50)   
AS
BEGIN

SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*) FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping' and last_batch< getdate()-0.0416
and spid>51 and loginname='jrzsfc' and program_name='SQL Query Analyzer'

WHILE @spid IS NOT NULL
  BEGIN
    SET @sql = 'KILL ' + RTRIM(@spid)
    EXEC ( @sql )

    SELECT @spid = MIN(spid),@cnt = COUNT(*)
    FROM master..sysprocesses
    WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'
    and last_batch< getdate()-0.0416 and spid>51 and loginname='jrzsfc' and program_name='SQL Query Analyzer'
  END

END

GO