I have already posted a blog post about the usage of sp_who2 in OPENROWSET function. It is not possible to use it because sp_who2 returns duplicate column names (SPID)
In this post we will analyse why it returns duplicate columns
The system procedure sp_who2 gives informations about processes, users, CPU Time etc.
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
----- ----------- -------- -------- ----- ------------ ---------------- ------- ------ -------------- ------------- ----- ---------
1 BACKGROUND sa . . NULL RESOURCE MONITOR 0 0 02/15 12:40:48 1 0
2 BACKGROUND sa . . NULL XE TIMER 0 0 02/15 12:40:48 2 0
3 BACKGROUND sa . . NULL XE DISPATCHER 0 0 02/15 12:40:48 3 0
4 BACKGROUND sa . . NULL LAZY WRITER 0 0 02/15 12:40:48 4 0
5 BACKGROUND sa . . NULL LOG WRITER 0 0 02/15 12:40:48 5 0
6 BACKGROUND sa . . NULL LOCK MONITOR 0 0 02/15 12:40:48 6 0
7 BACKGROUND sa . . master SIGNAL HANDLER 0 0 02/15 12:40:48 7 0
8 sleeping sa . . master TASK MANAGER 0 0 02/15 12:40:48 8 0
As you notice, there are two columns with the name SPID. Usually the resultset will never have a duplicated column name. So I wanted to know the reason and looked at the definition of sp_who2. In the procedure code it is specified as
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
So it is used only to have that column in the right side of the result grid. But it is not advisible to use that way as it can not be used as a resultset by other applications.