Breaking News: Grepper is joining You.com. Read the official announcement!

close database connection sql

Mohammed Raziq Ali answered on January 26, 2023 Popularity 8/10 Helpfulness 2/10

answer close database connection sql

close database connection sql

Comment

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KillConnectionsHost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KillConnectionsHost]
GO


/****** Object:  StoredProcedure [dbo].[KillConnectionsHost]    Script Date: 10/26/2012 13:59:39 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[KillConnectionsHost] @hostname varchar(MAX)
AS
    DECLARE @spid int
    DECLARE @sql varchar(MAX)

    DECLARE cur CURSOR FOR
        SELECT spid FROM sys.sysprocesses P
            JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
            JOIN sys.sysusers U ON (P.uid = U.uid)
            WHERE hostname = @hostname AND hostname != ''
            AND P.spid != @@SPID

    OPEN cur

    FETCH NEXT FROM cur
        INTO @spid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(varchar, @spid)

        SET @sql = 'KILL ' + RTRIM(@spid)
        PRINT @sql
        EXEC(@sql)

        FETCH NEXT FROM cur
            INTO @spid
    END

    CLOSE cur
    DEALLOCATE cur
GO

Popularity 8/10 Helpfulness 2/10 Language sql

Source: stackoverflow.com

Tags: database-connection sql

Link to this answer
Share Copy Link

Contributed on Jan 26 2023

Mohammed Raziq Ali

0 Answers Avg Quality 2/10

close database connection sql

Contents

More Related Answers

close database connection sql

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.