how-to kill all the connections to an sql server in tsql via a stored procedure

/****** Object:  StoredProcedure [dbo].[procUtils_KillAllSqlServerConnections]    Script Date: 03/23/2010 14:31:38 source:I HEART MICROSOFT BLOG  ******/

IF  EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_KillAllSqlServerConnections]') 

AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[procUtils_KillAllSqlServerConnections]

GO

/****** Object:  StoredProcedure [dbo].[procUtils_KillAllSqlServerConnections]    

See doc bellow 

Script Date: 03/23/2010 14:31:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[procUtils_KillAllSqlServerConnections]                            

@DbName varchar(200) 

AS                            

BEGIN -- proc start                            

SET NOCOUNT ON;                            

declare @ThisProcName varchar(200)        

declare @ProcStep varchar(4000)  

declare @msg nvarchar(max)

declare @debugmsg nvarchar(max)

declare @ret int   

-- use this variable while generating code 

DECLARE @WinNewLine nvarchar(2)

set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))

-- this is a horizontal tab

DECLARE @Tab nvarchar(1)

set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))

set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))        

set @Msg = ''     

set @DebugMsg = ''     

set @ProcStep = ''     

BEGIN TRY        --begin try                  

set @Ret = 1 --assume false from the beginning                  

set @Msg = 'The Msg To Show to the user'            

set @Ret = @@ERROR                  

declare @sqlCode nvarchar(max) 

set @sqlCode = ''

--================================================

-- START SHOW ALL THE CONNECTIONS 

set @sqlCode = @sqlCode +  

'USE MASTER ;

SELECT * FROM sysprocesses WHERE dbid = DB_ID(''' + @DbName + ''') ;'

-- END  SHOW ALL THE CONNECTIONS 

--================================================

--================================================

-- START KILL ALL THE CONNECTIONS 

set @sqlCode = @sqlCode +  

'USE master ;

DECLARE @dbname varchar(30), @spid varchar(10), @start datetime

SELECT @start = current_timestamp, @dbname = ''' + @DbName + '''

-- Timeout after 5 mts

while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND

datediff(mi, @start, current_timestamp) < 5)

begin

DECLARE spids CURSOR FOR

SELECT convert(varchar, spid) FROM sysprocesses

WHERE dbid = db_id(@dbname)

OPEN spids

while(1=1)

BEGIN

FETCH spids INTO @spid

IF @@fetch_status < 0 BREAK

exec(''kill '' + @spid)

END

DEALLOCATE spids

END ; '

-- END  KILL ALL THE CONNECTIONS 

--================================================

SELECT @sqlCode 

EXEC sp_executesql  @sqlCode

SET @sqlCode = ''

return @Ret                  

END TRY        --end try                  

BEGIN CATCH                        

PRINT 'In CATCH block.                         

Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '                        

Error message: ' + ERROR_MESSAGE() + '                        

Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '                        

Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '                        

XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));                        

set @Msg = 'An Error occured '             

set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +   @WinNewLine  +

'Error message: ' + @WinNewLine + ERROR_MESSAGE() +

'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +   @WinNewLine  +

'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) +   @WinNewLine  +

'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))    +   @WinNewLine  

--record the error in the database                        

set @DebugMsg = @DebugMsg + @Msg    

set @Ret = 1                       

END CATCH                        

return  @Ret                                   

END --PROC END

/*                   

<ProcName> procUtils_KillAllSqlServerConnections </ProcName> 

<doc>

Generates the Update Procedure on any table a

</doc> <use>

exec procUtils_KillAllSqlServerConnections @DbName = 'CAS_DEV'

</use> */ 

GO

-- START PERMISSIONS

--GRANT EXECUTE ON [dbo].[procUtils_KillAllSqlServerConnections] TO [ReplaceHereDbRole] AS [dbo]

GO

-- END PERMISSIONS