Store The Output Of A Stored Procedure In A Table Without Creating A Table

I saw this technique in the Inside Microsoft SQL Server 2005: T-SQL Querying T-SQL and today I saw it also here The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement @ Ward Pond's SQL Server blog

I decided to expand this a little
Sometimes I need to quickly find out who is blocking what (or what is blocking who for that matter) so I run sp_who2 then look at the BlkBy column and run a DBCC INPUTBUFFER or fn_get_sql to get the sql statement

When you have a lot of connections it's a pain in the neck to look for the BlkBy where it's not . since the result is ordered by SPID
You can always do
CREATE TABLE
INSERT TABLE
EXEC Proc

But who wants to create tables all the time (not me, at least not for this stuff)
SPID is twice in the resultset of sp_who2 (who know why?) so that complicates things a little
So let's start with sp_who


SELECT * INTO #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')

--return only the connections to master
SELECT * FROM #TempSpWho
WHERE dbname ='master'



--Let's try sp_who2
SELECT * INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')

--Oops
Server: Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'SPID' in table '#TempSpWho2' is specified more than once.

--No problem list the columns
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')

--Get the connections to master only
SELECT * FROM #TempSpWho2
WHERE dbname ='master'

--Get the blocking SPID's
SELECT * FROM #TempSpWho2
WHERE BlkBy NOT LIKE '% .'

--Get user connections only (everything with a SPID below 51 is reserved for sql server processes)
SELECT * FROM #TempSpWho2
WHERE SPID >= 50

A couple of notes
The OPENROWSET call opens a separate connection to SQL Server, so there is some overhead associated with this approach
This technique is not supported inside a declared transaction
Blocking can occure with a poorly architected stored procedure