我试图按照示例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,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("FOO\sql2012",'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,因为它可能为您提供实现目标的灵感.
