清除sqlserver2000数据库连接

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROCEDURE sp_KillUsers
@p_DBName SYSNAME = NULL
AS

/* Check Paramaters */ /* Check for a DB name */ IF (@p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables */ DECLARE @v_spid INT,
@v_SQL NVARCHAR(255)

/* Declare the Table Cursor (Identity) */ DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @v_SQL = 'KILL ' + CONVERT(NVARCHAR, @v_spid)
-- PRINT @v_SQL
EXEC (@v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO