Disconnecting SQL connections....

Is there a way-SQL Script wise to disconnect every application(ie. Crystal reports) or Users connected to a database?
7of9121098Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
Yes, there is.  Here is a Stored Procedure that I use when I need to kill all of the connections to a Database.  Pass it the name of the database that you want to kill connections on, e.g.:

EXEC usp_KillConnectionsOnDB 'pubs'

Create Procedure usp_KillConnectionsOnDB
@dbName varchar(40)
AS

Create Table #tmpKill(Cmd varchar(200))

Declare @LoopCount int
Declare @cmd varchar(200)
Declare @rows Int
Declare @dbid Int

Select @dbid = dbid From master.dbo.sysdatabases
WHERE [Name] = @dbName

Set @LoopCount = 0

CheckKill:

Insert Into #tmpKill (Cmd)
Select 'Kill ' + Cast(spid as varchar)
FROM master.dbo.sysprocesses
WHERE dbid = @dbid
AND RTrim(status) = 'sleeping'

Set @Rows = @@RowCount

If @Rows <> 0
BEGIN
      Declare c_KillMe CURSOR For
            Select cmd From #tmpKill

           Open c_Killme
           Fetch Next From c_Killme Into @Cmd
           While @@FETCH_STATUS = 0
           BEGIN
               Exec (@cmd)
               Fetch Next From c_Killme Into @Cmd
           END
           Close c_Killme
           Deallocate c_Killme
END

If Exists (Select spid FROM master.dbo.sysprocesses
WHERE dbid = @dbid)
BEGIN
      If @LoopCount < 30      -- Max 30 second wait
      Begin
            Waitfor Delay '000:00:01'
            Set @LoopCount = @LoopCount + 1
            Truncate Table #tmpKill
            Goto CheckKill
      End
      Else
      Begin
            Truncate Table #tmpKill
            Insert Into #tmpKill (Cmd)
                  Select 'Kill ' + Cast(spid As varchar)
                  FROM master.dbo.sysprocesses
                  WHERE dbid = @dbid

            If @@Rowcount <> 0
            BEGIN                  
                  Declare c_KillMe CURSOR For
                        Select cmd From #tmpKill
                  Open c_Killme
                  Fetch Next From c_Killme Into @Cmd
                  While @@FETCH_STATUS = 0
                  BEGIN
                        Exec (@cmd)
                        Fetch Next From c_Killme Into @Cmd
                  END
                  Close c_Killme
                  Deallocate c_Killme
            END
      End
End
Drop Table #tmpKill

GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arbertCommented:
In SQL 2000, you were given the ability to change properties on the database and kill all connections.....

This changes the database to readonly, killing all connections, and then changes the property back to read write.

alter database hwmf_db_idea set read_only with rollback immediate
go
alter database hwmf_db_idea set reqd_write with rollback immediate
go


Brett
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.