sp_who2

Discussion:

sp_who2

(too old to reply)

Rick Charnes

2006-09-18 14:41:56 UTC

Using SQL Server 2000, I've just started using SP_WHO2. In the older
SP_WHO, I could display only SPID's that pertain to my own login with:

SP_WHO "mylogin"

But I see that --

SP_WHO2 "mylogin"

returns *all* logins What am I missing? (I don't even see SP_WHO2 in
my version of BOL!)

Aaron Bertrand [SQL Server MVP]

2006-09-18 14:53:55 UTC

Permalink

Seems like either a bug or intentional behavior (e.g. @loginame left on to
not break code, not to continue to function as you might expect). I'll let
you know what I hear back, if anything.

A

Post by Rick Charnes
Using SQL Server 2000, I've just started using SP_WHO2. In the older
SP_WHO "mylogin"
But I see that --
SP_WHO2 "mylogin"
returns *all* logins What am I missing? (I don't even see SP_WHO2 in
my version of BOL!)

Kalen Delaney

2006-09-18 14:54:32 UTC

Permalink

sp_who is not an 'older' command, it is the documented command. sp_who2 has
always been an undocumented procedure, just an extension of sp_who that some
people found more useful.
However, the code in sp_who2 is not very robust and it does a much simpler
check to see if the login name you passed is valid. You can actually look at
the code for sp_who and sp_who2 (using sp_helptext) and see the difference.

So the only thing you are doing 'wrong' is using an undocumented procedure
and expecting it to behave a certain way. There are no guarantees with
sp_who2.
--
HTH
Kalen Delaney, SQL Server MVP

SQL Menace

2006-09-18 15:01:05 UTC

Permalink

you can pass in the SPID or 'active'

SP_WHO2 '1'
SP_WHO2 'active'

of course you can look at the code and create you own sp_who3

To look at the code run this master..sp_helptext 'SP_WHO2'
but that probably not recommended

in sql server 2005 you can use the sys.dm_exec_sessions dynamic
managment view

SELECT * FROM sys.dm_exec_sessions where login_name ='YourName'

or in 2000 you can do something like this

SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime,
DiskIO, LastBatch, ProgramName
INTO #TempSpWho2
FROM OPENROWSET
('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off
exec master.dbo.sp_who2')
AS tbl

SELECT * FROM #TempSpWho2
WHERE login ='YourName'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Roy Harvey

2006-09-18 15:16:51 UTC

Permalink

Along with what everyone else said, I just ran some tests against SQL
Server 2000, and found that using "double" quotes for the parameter
may be part of your problem. sp_who returns the same results when I
use 'single' or "double" quotes, but sp_who2 choked on the "double"
quotes.

Roy Harvey
Beacon Falls, CT

On Mon, 18 Sep 2006 10:41:56 -0400, Rick Charnes

Kalen Delaney

2006-09-18 15:25:01 UTC

Permalink

I couldn't duplicate these results. I had been using single quotes in all my
tests.
When I switched to double, there was no difference. sp_who2 with single or
double quotes gave the same results, but it was a different set of results
than I got with sp_who, with either single or double quotes.
--
HTH
Kalen Delaney, SQL Server MVP

Post by Roy Harvey
Along with what everyone else said, I just ran some tests against SQL
Server 2000, and found that using "double" quotes for the parameter
may be part of your problem. sp_who returns the same results when I
use 'single' or "double" quotes, but sp_who2 choked on the "double"
quotes.
Roy Harvey
Beacon Falls, CT
On Mon, 18 Sep 2006 10:41:56 -0400, Rick Charnes

Roy Harvey

2006-09-18 15:46:20 UTC

Permalink

Well I could not duplicate them either, so I sure I blew that one.
Sorry about that.

What actually happened is that with sp_who2 it is rejecting it for
both single and double quotes:

exec sp_who2 'PEREGRINE\Roy'
exec sp_who2 "PEREGRINE\Roy"

Server: Msg 15007, Level 16, State 1, Procedure sp_who2, Line 84
The login 'PEREGRINE\Roy' does not exist.
Server: Msg 15007, Level 16, State 1, Procedure sp_who2, Line 84
The login 'PEREGRINE\Roy' does not exist.

where PEREGRINE\Roy is straight from the output of sp_who.

As you said, "the code in sp_who2 is not very robust and it does a
much simpler check...".

Roy

On Mon, 18 Sep 2006 08:25:01 -0700, "Kalen Delaney"

Post by Kalen Delaney
I couldn't duplicate these results. I had been using single quotes in all my
tests.
When I switched to double, there was no difference. sp_who2 with single or
double quotes gave the same results, but it was a different set of results
than I got with sp_who, with either single or double quotes.

Tom Cooper

2006-09-18 16:11:16 UTC

Permalink

And if you look at the code for ssp_who2, you see that passing a login name
can't do anything (if it is a real login name). At about line 53 it
calculates @sidlow and @sidhigh as the suser_sid() of the @loginname you
passed. Then if @loginame was numeric, it assumes @loginname was a spid and
calculates @spidlow and @spidhigh from @loginname.

It, however, never uses @sidlow and @sidhigh (they are only referenced in
lines in a WHERE clause that have been commented out at about line 190).
So, in sp_who2, if you pass the parameter @loginame, and it is a valid login
name, sp_who2 ignores it, if it is not a valid login name, but it is an
integer, sp_who2 treats it as a spid, and if it is anything else, it returns
an error message.

All in all, not the best piece of code I've ever seen written <grin>.

Tom

Post by Kalen Delaney
I couldn't duplicate these results. I had been using single quotes in all
my tests.
When I switched to double, there was no difference. sp_who2 with single or
double quotes gave the same results, but it was a different set of results
than I got with sp_who, with either single or double quotes.
--
HTH
Kalen Delaney, SQL Server MVP

Aaron Bertrand [SQL Server MVP]

2006-09-21 16:55:57 UTC

Permalink

Post by Rick Charnes
SP_WHO2 "mylogin"
returns *all* logins What am I missing?

It's not you, it's them! :-)

Sorry about the delay. The official answer is that it is a known bug and
will not be fixed. (If you'd like to contact support about the issue and
appeal the decision or get more information, please reference bug #402871.)

In the meantime, my advice is two write your own version of sp_who2 if this
functionality is important to you. You can do this by issuing the
following:

USE master;
GO
EXEC sp_helptext sp_who2;

Now you have a create procedure script that you can modify (including
changing the name, of course) and

If you are targeting SQL Server 2005, I strongly suggest avoiding
sysprocesses. As BOL 2005 states:

"This Microsoft SQL Server 2000 system table is included as a view for
backward compatibility. We recommend that you use the SQL Server 2005 system
views instead."

[
From the topic sys.sysprocesses
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/60a36d36-54b3-4bd6-9cac-702205a21b16.htm
]

Since this was brought up in a private conversation with Microsoft people
involved, a sort of challenge was raised, and I have created a couple of
versions for public consumption here:

http://www.aaronbertrand.com/rant.asp?r=491

Note that I replicated sp_who2 as closely as possible, using the DMVs
instead of sys* tables, and without fixing the expected @loginame behavior.
But it would be trivial to do so, and maybe I will tackle it before the week
is out. I suppose I should also complete the cycle by creating a "fixed"
sp_who2 for SQL Server 2000, and adding the @loginame filtering to ab_who2
for SQL Server 2005. But I do have more pressing items on my plate at the
moment.

A