Я также разместил этот вопрос на SQL Server Central, и некоторые ответы заставили меня вернуться к поиску ответа в OPENROWSET (и найти его). Один из людей направил меня к разделу этой статьи, посвященному OPENQUERY. В нем говорится, что для решения проблемы с временными таблицами вы просто добавляете SET FMTONLY OFF в строку выполнения вашего оператора OPENQUERY/OPENROWSET следующим образом:

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

Однако, если процедура не имеет SET NOCOUNT ON, она все равно вызывает ошибку. У меня в голове возникло глупое недоразумение по поводу SET NOCOUNT ON, которое помешало мне подумать: «Эй, а нельзя ли просто добавить SET NOCOUNT ON в инструкцию выполнения OPENROWSET??» Как только кто-то задал этот вопрос для меня в другой ветке, это обрело слишком много смысла =) Итак, вот решение, которое я искал все это время:

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

Хорошо.. Я сдался и вернулся к моему старому другу xpcmdshell. В этом ответе и его коде подчеркивание (_) будет подразумеваться для xpcmdshell, поскольку я часто не могу загрузить страницы, содержащие полное имя.

Во-первых, вот только три вещи, которые я пробовал, которые НЕ работали (я не могу вспомнить все остальные):

• 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

Итак, после долгих головоломок и гугления, я вернулся к xpcmdshell. Следующий сценарий (который я буду превращать в процедуру) берет оператор exec SP и базу данных для его запуска, форматирует команду xpcmdshell sqlquery в файл, выполняет файл и вставляет его вывод во временную таблицу. затем извлекает заголовки столбцов этих результатов в другую временную таблицу.

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

Если вы планируете использовать этот код, не забудьте найти замену xpcmdshell на xp(_)cmdshell.

Надеюсь, это поможет кому-то! Пожалуйста, не стесняйтесь публиковать любые вопросы, комментарии или предложения, которые могут у вас возникнуть.

Вы используете переменную временной таблицы #T. Вы должны использовать временную таблицу @T. Насколько я понимаю, переменную временной таблицы нельзя использовать в среде распределенных транзакций, а также у вас может не быть доступа к базе данных TempDB на связанном сервере.