使用OPENQUERY(exec存储过程)创建新的临时表失败，错误为11526 [英] Using OPENQUERY (exec stored procedure) to create new temporary table fails with error 11526

问题描述

我在开发PC上安装了SQL Server 2012完整版.

I have SQL Server 2012 full version installed on my development PC.

我正在尝试在此处进行操作的示例>，其中显示了如何使用存储过程作为数据源来创建新的临时表.我正在尝试将几个存储过程的结果合并到一个临时表中(各种结果集的列结构/定义是相同的.)

I am trying to follow the examples here, which show how to create a new temporary table using a stored procedure as the source of the data. I am trying to combine the results of several stored procedures into a single temporary table (the column-structure/definition of the various resultsets is identical).

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

To test if the plumbing is working, I issue this query:

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

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

But I'm getting this error from that simple test-the-plumbing select query:

消息11526，级别16，状态1，过程sp_describe_first_result_set，第1行
无法确定元数据，因为语句'insert #tmp(foo1，foo2，foo3) 在过程'myStoredProc'使用临时表中选择'O'作为foo1，foo2，foo3'.

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取决于服务器能否从数据库中的 persistent 定义中提取列数据类型，以及在我存储的proc中实例化的临时表(临时表)，缺乏持久的定义.如果是这样，是否有任何设置可以告诉OPENQUERY尽其所能，并尝试对列数据类型做出明智的猜测?

If I understand the error correctly, OPENQUERY depends upon the server being able to extract the column datatypes from the persistent definition in the database, and the temporary table instantiated in my stored proc, being ephemeral, lacks a persistent definition. If that is the case, is there any setting that tells OPENQUERY to do the best it can and try to make an intelligent guess at the column datatypes?

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

Here's the dummy SP I'm testing with:

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;然后运行您的语句来实现.在不使用临时表的存储过程中，这可以很好地工作.查询解析器基本上可以进行试运行，而无需实际获取所有数据，仅获取元数据(有点像显示估计的执行计划).

The reason for this is that when you execute a stored procedure across a linked server, the provider first tries to determine the shape of the resulting rowset. It does this by issuing SET FMTONLY ON; and then running your statement. In a stored procedure that doesn't use temp tables, this works beautifully. The query parser basically does a dry run without actually fetching all the data, just the metadata (sort of like showing an estimated execution plan).

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

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

Be aware that using this method will make the stored procedure run twice. The first time to collect the metadata (the data being discarded), and the second time to actually return the data. If the called stored procedure is particularly costly or has side-effects, you may need to make allowances.

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

Finally, note that this trick doesn't work on every stored procedure. There are things stored procedures can do that just throw a wrench in the works. I don't know all the possibilities, but one of them is returning multiple recordsets.

响应您的更新，SET FMTONLY OFF不起作用:您是否可以重组SP以不使用临时表或使用会话键永久表?这些选项都可以胜任.在SQL Server 2012中，您还可以选择使用表值参数传递数据.

In response to your update that SET FMTONLY OFF doesn't work: can you possibly restructure your SP to not use a temp table, or to use a session-keyed permanent table? Either of these options could do the job. In SQL Server 2012, you also have the option of passing around data with table-valued parameters.

您可能想阅读Erland Sommarskog的如何在存储过程之间共享数据，因为它可能会为您提供灵感达到目的的方法.

You might like to read Erland Sommarskog's How to Share Data between Stored Procedures as it might provide you with inspiration for a way to accomplish your purpose.

这篇关于使用OPENQUERY(exec存储过程)创建新的临时表失败，错误为11526的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！