SQL 2005 Find Out Who Are Connected

Problem:

I want to provide a list of currently connected SQL clients on my asp.net based web page, like the version of Activity Monitor that comes in the SQL Management Studio.

Solution:

The information are in the following queriable objects. For me, sp_who is good enough as I just need to list who are connected in my application monitor web app.

• exec sp_who
• exec sp_who2
• select * from master..sysprocesses

and finally this is supposed to give you the same list as what you see in the Activity Monitor (I did not try).

DECLARE @CMD VARCHAR(8000) DECLARE @ID int
SET @ID = @@SPID SET @CMD = '
EXEC sp_MSset_current_activity ' + CAST(@id as varchar) + '
SELECT * FROM ##lockinfo' + CAST(@id as varchar) + '
SELECT * FROM ##procinfo' + CAST(@id as varchar)
EXEC (@CMD)

Time Saved:

This information is either too obvious to DBAs or such that query to Google or Live does not provide an immediate answer. Took me about 30 min to finally found out. The keyword to search is "Activity Monitor"