metadata - 使用 OPENQUERY(exec 存储过程)创建新临时表失败，错误为 11526

Question

我的开发PC上安装了SQL Server 2012完整版。

我正在尝试遵循示例here，该示例显示了如何使用存储过程作为数据源来创建新的临时表。我试图将几个存储过程的结果合并到一个临时表中(各种结果集的列结构/定义是相同的)。

要测试管道是否正常工作，我发出以下查询:

 SELECT * FROM OPENQUERY("FOO\SQL2012", 'exec mySchema.myStoredProc')

但是我从一个简单的管道测试选择查询中得到了这个错误:

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'insert #tmp(foo1, foo2, foo3) select 'O' as foo1, foo2, foo3' in procedure 'myStoredProc' uses a temp table.

如果我正确理解该错误，则OPENQUERY取决于服务器是否能够从数据库的持久性定义中提取列数据类型，并且在我存储的临时过程中实例化的临时表(暂时的)缺少持久性定义。在这种情况下， 是否有任何设置可以告诉OPENQUERY尽其所能，并尝试对列数据类型做出明智的猜测？

这是我正在测试的虚拟SP:

create proc testproc
as
begin

create table #test
(id int, name varchar(5) );

insert into #test(id,name)values(1,'xxx');
select * from #test;
--drop table #test;   -- tried dropping and not dropping, same error either way
end

Answer 1

试试这个:

SELECT *
FROM OPENQUERY("FOO\SQL2012", 'SET FMTONLY OFF; EXEC mySchema.myStoredProc;') X;

这样做的原因是，当您跨链接服务器执行存储过程时，提供程序首先尝试确定结果行集的形状。它通过发出 SET FMTONLY ON;然后运行您的语句来做到这一点。在不使用临时表的存储过程中，这可以很好地工作。查询解析器基本上可以进行试运行，而无需实际获取所有数据，仅获取元数据(类似于显示估计的执行计划)。

问题在于，当存储过程确实使用临时表时，它会失败，因为临时表的元数据不存在:无法通过适用于不使用临时表的存储过程的元分析来收集它。然后，解决方法是在执行存储过程的批处理中手动 SET FMTONLY OFF;。

请注意，使用此方法将使存储过程 运行两次。第一次收集元数据(数据被丢弃)，第二次实际返回数据。如果被调用的存储过程特别昂贵或有副作用，则可能需要留出一定的余地。

最后，请注意，此技巧不适用于每个存储过程。存储过程可以做一些事情，只需花些力气即可。我不知道所有的可能性，但是其中之一是返回多个记录集。

作为对您的更新的回应， SET FMTONLY OFF不起作用:您是否可以重组SP以不使用临时表或使用 session 键永久表？这些选项都可以胜任。在SQL Server 2012中，您还可以选择使用 table-valued parameters传递数据。

您可能想阅读Erland Sommarskog的 How to Share Data between Stored Procedures，因为它可能为您提供实现目标的灵感。