sql - 当 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 代码时，我收到以下错误:

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.

当我将 OPENROWSET 代码(通过删除动态内容)修剪为:

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

我收到以下(更有用的)错误:

Invalid object name '#T'.

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

所以我被引导到..

我想问你的问题

有人知道有什么可能的方法来规避这个错误吗？或者有其他解决方案吗？

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

任何意见都将不胜感激。

谢谢，佐克

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

Answer 1

我也在 SQL Server Central 上发布了这个问题，一些回复让我又开始在 OPENROWSET 中寻找答案(并找到它)。其中一个人向我求助 this article有关 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'
                  )