Gehe zu deutscher Webseite

ViaThinkSoft CodeLib

This article is in:
CodeLibProgramming aidsSQL Server

First of all, you need:

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @AllConnections EXEC sp_who2

Then, if you want to check if any other computer or user is accessing the database:

SELECT * FROM @AllConnections WHERE DBName = ( select DBName from @AllConnections where SPID_1 = @@spid )
and LOGIN+'@'+HostName not in ( select LOGIN+'@'+HostName from @AllConnections where SPID_1 = @@spid )

If you want to check if any other connection is open (even if it is on the same computer or the same app), use this:

SELECT * FROM @AllConnections WHERE DBName = ( select DBName from @AllConnections where SPID_1 = @@spid )
and SPID_1 <> @@spid
Daniel Marschall
ViaThinkSoft Co-Founder

Feedback? Problems? Please drop us a message!

Your name (optional):

E-mail address (optional):

Message: