当 SP 包含 # 个临时表时，使用 OPENROWSET 动态检索 SP 结果

Question

我的场景

我正在开发一个数据库，该数据库将包含整个服务器上不同数据库中的各种存储过程的许多详细信息。我现在试图收集的信息是“SP 输出什么？”

在搜索中我发现答案就在 OPENROWSET 中。我的初步测试很成功，一切看起来都很棒。然而，在使用实时 SP 对其进行测试时，我遇到了一个主要问题：它不能很好地与临时 (#) 表配合使用。

例如：

如果我要采用此 SP：

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

我可以使用以下代码轻松地将输出插入到临时（##）表中，然后查询 tempdb 的 sysobjects 并生成列及其数据类型的列表：

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

太棒了！但是，如果 SP 是这样的：

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

当我执行与之前相同的 OPENROWSET 代码时，我收到以下错误：

无法处理对象“SET FMTONLY OFF exec DatabaseName.dbo.zzTempSP @A=1,@B=2”。链接服务器“(null)”的 OLE DB 提供程序“SQLNCLI10”指示该对象没有列，或者当前用户对该对象没有权限。

当我将 OPENROWSET 代码（通过删除动态内容）修剪为：

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

我收到以下（更有用）错误：

对象名称“#T”无效。

这就是我碰壁的地方。在我的搜索中，似乎没有解决办法，但我还不能让自己放弃它。

所以我被引导到..

我向你提出的问题

有人知道有任何可能的方法来规避这个错误吗？或者有可能有替代解决方案吗？

这个过程不会经常运行，所以我不必太担心解决方案的效率。

任何意见将不胜感激。

谢谢， Zok

PS：抱歉格式问题。我不太明白语言标签。

Answer 1

我也在 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'
                  )

Answer 2

好吧..我已经放弃并回到我的老朋友 xpcmdshell 那里了。在整个响应及其代码中，xpcmdshell 将隐含下划线 (_)，因为我经常无法加载包含全名的页面。

首先，这里只是我尝试过但不起作用的三件事（我不记得所有其他的了）：

• SET NOCOUNT ON
  • 适用于任何没有临时表的 SP，但对于 2500+ 中的大多数 SP，我将仔细研究如何利用它们，这是不可行的。
• 无操作
  • 我创建了一个过程来动态创建 No Op，但是在实现后我无法找到解决 SQL 陷入嵌套循环的方法。
• bcp 查询输出
  • 输出不包含标头

因此，经过大量的头部攻击和谷歌搜索后，我又回到了 xpcmdshell。以下脚本（我将把它变成一个过程）采用 SP exec 语句和运行它的数据库，将 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

希望这对某人有帮助！请随时发布您的任何问题、意见或建议。

Answer 3

您正在使用临时表变量#T。您必须使用临时表@T。
根据我的理解，临时表变量不能在分布式事务环境中使用，而且您可能无法访问链接服务器中的 TempDB。

Answer 4

我认为有些程序以 openrowset 方式执行非常棘手，

例如其中之一是 sp_helpcolumns。

如果我按照正常方式操作：

                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT OFF


IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL 
DROP TABLE #Radhe

SELECT * INTO #radhe
FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes'
, 'set fmtonly off;
DECLARE 
  @publication1                sysname=''my_db_Reporting'',  
  @article1                    SYSNAME=''tblAccountContact'',  
  @publisher                  sysname=null


exec my_db_Reporting.dbo.sp_helparticlecolumns 
      @publication=@publication1
     ,@article =@article1
WITH RESULT SETS( 
(
                 [column id]    int         
                ,[column]   sysname   
                ,published bit
                ,[publisher type]   sysname 
                ,[subscriber type]  sysname 
 )
 )
')

我收到此错误消息：

消息 7357，级别 16，状态 1，第 12 行无法处理对象“set”
立即关闭；

还有这个：

链接服务器“(null)”的 OLE DB 提供程序“MSOLEDBSQL”表示
该对象没有列或者当前用户没有
拥有该对象的权限。

Theb我找到了解决方法：

DECLARE 
  @publication                sysname='my_db_Reporting',  
  @article                    SYSNAME='tblAccountContact',  
  @publisher                  sysname=null

declare @Radhe table ( 

                 [column id]    int         
                ,[column]   sysname   
                ,published bit
                ,[publisher type]   sysname 
                ,[subscriber type]  sysname 
 )


insert into @Radhe
exec my_db_Reporting.dbo.sp_helparticlecolumns 
     @publication,@article 

select * from @Radhe