Programatically disconnect users from SQL

  • Thread starter Thread starter Marc Jennings
  • Start date Start date
M

Marc Jennings

Hi,

I need to rebuild a database each time I redeploy a test applicatio,
and I was wondering if anyone could give me some clues as to how to go
about disconnecting any users that may be logged into that database in
SQL. I know I can do this quite easily using MSBuild, but the reason
I need to know is so I can get awayt from using beta tools in
development. (We have a specific issue with MSBuild)

Also, if anyone knows of a good resource to help me deploy that
database as a whole, could they point me toward it, please?

TIA
Marc.
 
You can create a stored procedure like this and just call it from your
client code:

CREATE PROCEDURE usp_killDBConnections @DBName varchar(50), @withmsg bit=1
AS
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)

SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

IF @withmsg =1
PRINT CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '
+ @DBName

GO
 
Back
Top