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

From Wiki

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

tsqlLine number On/Off | Show/Hide | Select all

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 occur with a poorly architected stored procedure

Contributed by: --SQLDenis 03:02, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks