none
What is difference between SP_WHO & SP_WHO2?

Answers

  • sp_who is documented and officially supported. sp_who2 is undocumented and thus unsupported, but widely used. They return the same information: the processes currently active in SQL Server, but sp_who2 adds some extra columns which sp_who does not include. Furthermore, sp_who2 makes an effort to make the display to be as compact as possible for output in text mode. As Kalman notes, it adds extra spid columns for increase legibility.

    DBCC INPUTBUFFER returns the most recently submitted command for a process. spid is short for "server process id", or "session_id" as Microsoft prefers to call it these days. Whether you take the spid from sp_who or sp_who2 does not matter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 02, 2012 9:43 AM

All replies

  • For the difference between SP_WHO and SP_WHO2 check

    http://morrisbahrami.blogspot.com/2011/06/spwho-and-spwho2.html


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Saturday, June 02, 2012 6:25 AM
  • sp_who2 was done by a fool who duplicated the SPID column. Look what happens:

    -- DATA ACCESS must be turned on
    SELECT * FROM OPENQUERY(YOURSERVER, 'exec sp_who')
    -- (54 row(s) affected)
    SELECT * FROM OPENQUERY(YOURSERVER, 'exec sp_who2')
    /*
    Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
    The metadata could not be determined because statement 'delete #tb1_sysprocesses
             where   lower(status)  = 'sleeping'
             and     upper(cmd)' in procedure 'sp_who2' uses a temp table.
    */
    OPENQUERY errors out because 2 SPID columns.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, June 02, 2012 6:51 AM
    Moderator
  • sp_who is documented and officially supported. sp_who2 is undocumented and thus unsupported, but widely used. They return the same information: the processes currently active in SQL Server, but sp_who2 adds some extra columns which sp_who does not include. Furthermore, sp_who2 makes an effort to make the display to be as compact as possible for output in text mode. As Kalman notes, it adds extra spid columns for increase legibility.

    DBCC INPUTBUFFER returns the most recently submitted command for a process. spid is short for "server process id", or "session_id" as Microsoft prefers to call it these days. Whether you take the spid from sp_who or sp_who2 does not matter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 02, 2012 9:43 AM
    • Edited by SQL Champ Saturday, June 02, 2012 9:56 AM
    Saturday, June 02, 2012 9:56 AM