元数据 – 使用OPENQUERY(exec存储过程)创建新临时表失败,错误11

我试图按照示例here,它显示如何使用存储过程作为数据源创建新的临时表.我试图将几个存储过程的结果合并到一个临时表中(各种结果集的列结构/定义是相同的).

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

SELECT * FROM OPENQUERY("FOOSQL2012",'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,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

解决方法

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

这样做的原因是,当您跨链接服务器执行存储过程时,提供程序首先尝试确定生成的行集的形状.它通过发出SET FMTONLY ON来实现;然后运行你的声明.在不使用临时表的存储过程中,这非常有效.查询解析器基本上执行干运行而不实际获取所有数据,只是元数据(有点像显示估计的执行计划).

问题是当存储过程确实使用临时表时,它会失败,因为临时表的元数据不存在：无法通过适用于不使用临时表的存储过程的元分析来收集它.然后,固化是手动设置FMTONLY OFF;在执行存储过程的批处理中.

请注意,使用此方法将使存储过程运行两次.第一次收集元数据(被丢弃的数据),第二次实际返回数据.如果被调用的存储过程特别昂贵或有副作用,您可能需要考虑.

最后,请注意,此技巧不适用于每个存储过程.有些东西存储过程可以做,只是在工作中抛出一把扳手.我不知道所有的可能性,但其中一个是返回多个记录集.

为了响应您的更新,SET FMTONLY OFF不起作用：您是否可以重构SP以不使用临时表,或使用会话密钥永久表？这些选项中的任何一个都可以完成这项工作.在SQL Server 2012中,您还可以选择使用table-valued parameters传递数据.

您可能希望阅读Erland Sommarskog的How to Share Data between Stored Procedures,因为它可能为您提供实现目标的灵感.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!