Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
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
- 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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.