Using OPENROWSET to dynamically retrieve SP result

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'
                  )

Answer 2

Okay.. I've given up and gone back to my old friend xpcmdshell. Throughout this response and its code the underscore (_) will be implied for xpcmdshell as I often can't load pages containing the full name.

First, here are just three of the things I've tried that did NOT work (I can't recall all of the others):

• SET NOCOUNT ON
  • Works for any SP without temp tables, but as most of the 2500+- I'll be looking through make use of them this isn't feasible.
• No Op
  • I created a procedure to dynamically create a No Op, however upon implementation I was unable to find a way around SQL getting stuck in a nesting loop.
• bcp queryout
  • Output doesn't include headers

And so, after much head bashing and Googling, I've fallen back to xpcmdshell. The following script (which I'll be turning into a procedure) takes an SP exec statement and the database to run it under, formats an xpcmdshell sqlquery command into a file, executes the file and inserts the output of it into a temp table, then extracts the column headers of those results into another temp table.

SET NOCOUNT ON

DECLARE    @TempCmdPath VARCHAR(MAX),
        @ProcedureExec VARCHAR(MAX),
        @DatabaseName VARCHAR(255)

SELECT    @TempCmdPath = 'C:\Temp\' --Make sure path ends with a '\' (or add logic to append if missing)

SELECT    @ProcedureExec = 'exec dbo.crp_rpt_GetCustomerDetails @ShowContacts=0,@CustomerName=''cust123%''' --Make sure to double up the single quotes (')
SELECT    @ProcedureExec = REPLACE(@ProcedureExec, '''', '''''') --Double the single quotes again (') for use in xpcmdshell sqlquery command

SELECT    @DatabaseName = 'CorpDB'


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

CREATE TABLE dbo.#CmdOut
    (
      id INT IDENTITY(1,1), --Used in ROW_NUMBER() function to update rid
      rid INT, --Actual number for use in WHILE loop
      LineOut VARCHAR(MAX)
    )


DECLARE    @cmdshell VARCHAR(MAX)

/* Create a file with the commands to run */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''sqlcmd '
                    + REPLACE( '-q "PRINT '':error ' + @TempCmdPath + 'TempSqlCmdOut.txt'' ' --Set errors to be directed to a text file
                                    + 'PRINT ''' + @ProcedureExec + '''" ' --Add additional PRINT statements to include more statements to run
                                + '-o "' + @TempCmdPath + 'TempSqlCmd.txt" ' --Specify where the file should output to
                               , '''', '''''' ) --Double up the single quotes (') /again/ for this statement
                    + '''' --Close the statement

PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )


/* Execute the commands stored in the file we just created */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''sqlcmd '
                    + '-d ' + @DatabaseName + ' '
                    + '-r 1 ' --Set any additional messsages to be treated as errors.  This, combined with the ":error <path>\TempSqlCmdOut.txt" line above, will ensure that print statements are not returned in the output
                    + '-i "' + @TempCmdPath + 'TempSqlCmd.txt" '
                    + '-s "," ' --Column Separator
                    + '''' --Close the statement

PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )


/* Clean up. Delete the two temp files */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''del "' + @TempCmdPath + 'TempSqlCmd.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )

SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''del "' + @TempCmdPath + 'TempSqlCmdOut.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )



/* Clean up NULL rows then update the rid column's value */
DELETE    dbo.#CmdOut
WHERE    LineOut IS NULL

UPDATE    co
SET        rid = n.rid
FROM    dbo.#CmdOut co
        INNER JOIN (    SELECT    id,
                                ROW_NUMBER() OVER ( ORDER BY id ) AS [rid]
                        FROM    dbo.#CmdOut
                   ) AS n ON co.id = n.id


--SELECT * FROM dbo.#CmdOut

---------------------------------------------------------------
---------------------------------------------------------------

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

CREATE TABLE dbo.#SPResultHeaders
    (
      id INT IDENTITY(1,1),
      HeaderName VARCHAR(500)
    )


DECLARE    @LineCount INT,
        @LineIndex INT,
        @Delimiter VARCHAR(10),
        @PrevDelimitCharIndex INT,
        @NextDelimitCharIndex INT,
        @LineText VARCHAR(MAX),
        @EndOfLineText VARCHAR(MAX),
        @FoundDivider BIT

SELECT    @Delimiter = ',',
        @FoundDivider = 0

SELECT    @LineCount = COUNT(*),
        @LineIndex = 1
FROM    dbo.#CmdOut

/* Until we move through all of the output lines OR we run into the line between the headers and their data (divider).. */
WHILE ( @LineIndex <= @LineCount
        AND @FoundDivider = 0
      )
    BEGIN
        /* Reset DelimitCharIndex: */
        SELECT    @PrevDelimitCharIndex = 0,
                @NextDelimitCharIndex = 1

        /* Until the Delimiter is not found.. */
        WHILE ( @NextDelimitCharIndex <> 0
                AND @FoundDivider = 0
              )
            BEGIN
                /* Search for the Delimiter starting after the last one's position */
                SELECT    @NextDelimitCharIndex = CHARINDEX(@Delimiter, LineOut, @PrevDelimitCharIndex)
                FROM    dbo.#CmdOut
                WHERE    rid = @LineIndex

                /* If another Delimiter is found on this line.. */
                IF ( @NextDelimitCharIndex <> 0 OR @EndOfLineText IS NOT NULL )
                    BEGIN
                        /* Make sure we're don't have left overs from a previous line */
                        IF ( @EndOfLineText IS NOT NULL )
                            BEGIN
                                /* If we do, set the current string to the previous + the current */
                                SELECT    @LineText = @EndOfLineText + SUBSTRING(LineOut, @PrevDelimitCharIndex, (@NextDelimitCharIndex - @PrevDelimitCharIndex))
                                FROM    dbo.#CmdOut
                                WHERE    rid = @LineIndex

                                /* Then clear out the left overs */
                                SELECT    @EndOfLineText = NULL
                            END
                        ELSE
                            BEGIN
                                /* Get the text between the previous delimiter and the next */
                                SELECT    @LineText = SUBSTRING(LineOut, @PrevDelimitCharIndex, (@NextDelimitCharIndex - @PrevDelimitCharIndex))
                                FROM    dbo.#CmdOut
                                WHERE    rid = @LineIndex
                            END

                        /* After the column headers in the output it will have a divider consisting of hyphens (-) (split by whatever we specified for the -s argument of the sqlcmd)
                            Check to see if our text is purely hyphens. IF NOT, insert the text into our result table and increment Header Count by 1.  IF SO, set the FoundDivider flag to 1.
                        */
                        IF ( LTRIM(RTRIM(REPLACE(@LineText, '-', ''))) <> '' )
                            BEGIN
                                IF ( CHARINDEX('-', @LineText) <> 0 )
                                    BEGIN
                                        /* If there are more than three hyphens in a row, assume it's the divider and set @FoundDivider to 1 to exit while */
                                        IF ( SUBSTRING(@LineText, CHARINDEX('-', @LineText), 3) = '---' )
                                                SELECT    @FoundDivider = 1
                                        ELSE
                                            INSERT INTO dbo.#SPResultHeaders ( HeaderName )
                                                    SELECT    LTRIM(RTRIM(@LineText))
                                    END
                                ELSE
                                    BEGIN
                                        INSERT INTO dbo.#SPResultHeaders ( HeaderName )
                                                SELECT    LTRIM(RTRIM(@LineText))
                                    END
                            END
                        ELSE
                            BEGIN
                                /* If there are more than three hyphens in a row, assume it's the divider and set @FoundDivider to 1 to exit while */
                                IF ( SUBSTRING(@LineText, CHARINDEX('-', @LineText), 3) = '---' )
                                        SELECT    @FoundDivider = 1
                            END
                    END
                /* If another Delimiter is NOT found on this line.. */
                ELSE
                    BEGIN
                        /* Move remainder of this line's text to @EndOfLineText ("left overs") for use in next itteration */
                        SELECT    @LineText = NULL,
                                @EndOfLineText = SUBSTRING(LineOut, @PrevDelimitCharIndex, (LEN(LineOut) + 1))
                        FROM    dbo.#CmdOut
                        WHERE    rid = @LineIndex
                    END

                /* Update previous Delimiter's position */
                SELECT    @PrevDelimitCharIndex = @NextDelimitCharIndex + 1
            END --WHILE ( @NextDelimitCharIndex <> 0 )

        SELECT    @LineIndex = @LineIndex + 1
    END --WHILE ( @LineIndex <= @LineCount )


SELECT    *
FROM    dbo.#SPResultHeaders

If you plan to use this code, don't forget to do a find replace for xpcmdshell to xp(_)cmdshell

Hope this helps someone! Please don't hesitate to post any questions, comments, or suggestions you may have.

Answer 3

You are using a Temp table variable #T. You have to use a Temporary table @T. As per my understanding, Temp table variable can not be used in distributed transaction environment and also, that you might not have access to the TempDB in the linked server.