Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

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

From Wiki

Jump to: navigation, search

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


  1. SELECT * INTO #TempSpWho
  2.     FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
  3.      
  4.     --return only the connections to master
  5.     SELECT * FROM #TempSpWho
  6.     WHERE dbname ='master'
  7.      
  8.      
  9.      
  10.     --Let's try sp_who2
  11.     SELECT * INTO #TempSpWho2
  12.     FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
  13.      
  14.     --Oops
  15.     Server: Msg 2705, LEVEL 16, STATE 3, Line 1
  16.     COLUMN NAMES in EACH TABLE must be UNIQUE. COLUMN name 'SPID' in TABLE '#TempSpWho2' IS specified more THAN once.
  17.      
  18.     --No problem list the columns
  19.     SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName
  20.     INTO #TempSpWho2
  21.     FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
  22.      
  23.     --Get the connections to master only
  24.     SELECT * FROM #TempSpWho2
  25.     WHERE dbname ='master'
  26.      
  27.     --Get the blocking SPID's
  28.     SELECT * FROM #TempSpWho2
  29.     WHERE BlkBy NOT LIKE '% .'
  30.      
  31.     --Get user connections only (everything with a SPID below 51 is reserved for sql server processes)
  32.     SELECT * FROM #TempSpWho2
  33.     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

183 Rating: 3.0/5 (4 votes cast)