here's my other proc: note that it "toggles" the setting from single user to multi user for multiple calls;
that way if i need exclusive access, i can get it do stuff and put it back;
CREATE PROCEDURE sp_Kill2
@DBNAME VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DBID INT
SELECT @DBID=DB_ID(@DBNAME)
IF @DBID IS NULL
BEGIN
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
RETURN 1
END
IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc = 'MULTI_USER')
BEGIN
DECLARE @sql varchar(2000)
SET @sql = 'ALTER DATABASE '
+ quotename(@DBNAME)
+ ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
PRINT @sql
EXEC(@sql)
END
ELSE IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc <> 'MULTI_USER')
BEGIN
SET @sql = 'ALTER DATABASE '
+ quotename(@DBNAME)
+ ' SET MULTI_USER'
PRINT @sql
EXEC(@sql)
END
END --PROC
Lowell