Using OPENROWSET to dynamically retrieve SP results when SP contains # temp tables

Question

My Scenario

I'm working on a database which will contain many details from various Stored Procedures in different databases across the entire server. The information I'm attempting to gather now is, "What does the SP output?"

In searching I've found that the answer lies in OPENROWSET. My initial testing was successful and everything looked great. However, upon testing it with live SPs I ran into one major problem: It doesn't play well with temp (#) tables.

For example:

If I were to take this SP:

CREATE PROCEDURE dbo.zzTempSP(@A INT, @B INT) AS
SELECT @A AS A, @B AS B

I can easily insert the output into a temp (##) table with the following code, then query tempdb's sysobjects and produce a list of the columns and their data types:

IF OBJECT_ID('tempdb.dbo.##TempOutput','U') IS NOT NULL DROP TABLE ##TempOutput

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'SELECT * 
               INTO ##TempOutput
               FROM OPENROWSET(''SQLNCLI'', ''Server=' + 
        CONVERT(VARCHAR(100), SERVERPROPERTY('MachineName')) +
                             ';Trusted_Connection=yes;'', ''SET FMTONLY OFF exec ' + 
                               DB_NAME() + 
                              '.dbo.zzTempSP @A=1, @B=2'')'
EXEC(@sql)

SELECT *
FROM ##TempOutput

Great! However, if the SP was this instead:

CREATE PROCEDURE dbo.zzTempSP (@A INT, @B INT) AS CREATE TABLE dbo.#T (A INT, B INT)

INSERT INTO dbo.#T
SELECT   @A AS A, @B AS B

SELECT *
FROM dbo.#T

When I execute the same OPENROWSET code as before I receive the following error:

Cannot process the object "SET FMTONLY OFF exec DatabaseName.dbo.zzTempSP @A=1,@B=2". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

When I trim down the OPENROWSET code (by removing the dynamic stuff) to this:

SELECT   *
FROM OPENROWSET('SQLNCLI','Server=ServerName;Trusted_Connection=yes;',
                          'exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
              )

I receive the following (much more useful) error:

Invalid object name '#T'.

Which is where I hit the wall. In my searching it seems that there is no solution, but I couldn't bring myself to give up on it just yet.

And so I'm led to..

My question to you

Is anyone aware of any possible way to circumvent this error? Or is there possibly an alternative solution?

This process won't be run frequently so I needn't worry too much about the solution's efficiency.

Any input would be greatly appreciated.

Thanks, Zok

PS: Sorry about the formatting. I didn't quite figure out the language tags.

Answer 1

I had this question posted on SQL Server Central as well and some responses turned me back to looking for an answer within OPENROWSET (and finding it). One of the people turned me to this article's section on OPENQUERY. It states that in order to work around the issue with temp tables you simply add SET FMTONLY OFF to the execute line of your OPENQUERY/OPENROWSET statement like so:

SELECT  *
FROM    OPENROWSET( 'SQLNCLI',
                    'Server=SERVERNAME;Trusted_Connection=yes;',
                    'SET FMTONLY OFF; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
                  )

However, if the procedure does not have SET NOCOUNT ON specified it still raises an error. I had a silly misunderstanding about SET NOCOUNT ON in the back of my head that stopped me from thinking, "Hey, can't I just add SET NOCOUNT ON to the execute statement of OPENROWSET??" Once someone asked that question for me on the other thread it made all too much sense =) So, here is the solution I've been looking for all along:

SELECT  *
FROM    OPENROWSET( 'SQLNCLI',
                    'Server=SERVERNAME;Trusted_Connection=yes;',
                    'SET FMTONLY OFF; SET NOCOUNT ON; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
                  )