Using OPENQUERY (exec stored procedure) to create new temporary table fails with error 11526

Question

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:

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.

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?

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

Answer 1

Try this:

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

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).

The problem is that when the stored procedure does use temp tables, it fails, because the temp table's metadata doesn't exist: it can't be collected through the meta-analysis that works for stored procedures that don't use temp tables. The cure, then, is to manually SET FMTONLY OFF; within the batch that is executing the stored procedure.

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.

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.

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.