I downloaded this some time ago & found it to be a very useful tool. I created a file with various statments using sp_who1 as suggested. This has become an important part of my toolbox of utilities.
Thanks!

howardr 6/9/2006 12:07:45 PM

sp_who1 @loginame = [domain\\username] won't work if the user is accessing via an NT group. Comment out the below and all is OK:
if (@loginame is not null)
begin
if not exists (select 1 from master.dbo.syslogins with (nolock) where name = @loginame)
begin
raiserror(15007, -1, -1, @loginame)
return (1)
end
end

Highway 4/1/2005 9:28:37 AM

Very Nice!

If you like sp_who1, you should try sp_who_3:

http://www.sqlservercentral.com/scripts/contributions/1232.asp

It got like a perfect 5 stars and seems to be more efficient, more powerful, and has even more filtering and even include ad-hoc sorting. Check it out and post back comments.

This type of procedure has been desperately needed. I think Microsoft should release something like this in a service pack or in SQL 2005. There are plenty of sp_who_x out there on the web for free download.

Microsoft should just pick the best one and include it.


capone 3/2/2005 7:52:54 PM

Excellent article. Thanks for your brains and hard work.

PatrickCurry 3/1/2005 9:08:10 AM

Very useful. FYI: The web code download wraps two long comment lines leading to parse errors but these are easily solved by re-joining the long lines. Richard: I thank you very much for creating/sharing this code. Very useful!

flexdba 2/28/2005 4:05:52 PM

Hmm. The example above is far worse than reality - ignore the example, the web formatting's has made it even worse.

pyale 2/28/2005 10:41:23 AM

Example of the above:

SPID,status,loginame,dbname,command,hostname,memusg,phys_io,login_time,last_batch,SPID,program,cpu,opentran,blkBy,waittime,lastwaittype,waitresource,SPID
56,sleeping ,REACH\\philip.yale,master,DELETE ,REA0491L-IBM,8,4,02/28/2005 17:14:42,02/28/2005 17:14:42,56,SQL Query Analyzer ,30,2,55,938159,LCK_M_U,KEY: 9:405576483:1 (ed0175ce1a7b),56
57,sleeping ,REACH\\philip.yale,master,SELECT ,REA0491L-IBM,5,0,02/28/2005 17:14:42,02/28/2005 17:14:42,57,SQL Query Analyzer ,631,0,55,910820,LCK_M_S,KEY: 9:405576483:2 (c000178c23b1),57
55,sleeping ,REACH\\philip.yale,master,UPDATE ,REA0491L-IBM,14,16,02/28/2005 17:14:41,02/28/2005 17:19:40,55,SQL Query Analyzer ,831,2,53,960241,LCK_M_U,KEY: 9:1977058079:1 (100198c3f985),55

pyale 2/28/2005 10:40:08 AM

Very good overall. The only disappointment was that, when I run it at least, I don't get formatted output in TEXT mode like your example shows - mine's all strung together so that the columns don't line up with the headers. I can run it in GRID mode, but then I lose the output messages (like "Head(s) of blocking chain is(are):" )

pyale 2/28/2005 10:25:14 AM

Good article. Will definitely use this proc

srini_kris 2/24/2005 11:56:24 AM