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

Question

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

I am trying to follow the examples here that show how to create a new temporary table using a stored procedure as a data source. I am trying to combine the results of several stored procedures into one temporary table (column-structure / definition of different result sets is the same).

To check if the plumbing works, I issue this request:

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

But I get this error from this simple sample test select query:

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
Metadata cannot be determined because the operator "insert #tmp (foo1, foo2, foo3) select" O "as foo1, foo2, foo3 'in the procedure" myStoredProc "uses a temporary table.

If I understand the error correctly, OPENQUERY depends on how the server can retrieve column data types from a permanent definition in the database, and the temporary table created in my stored procedure, being ephemeral, does not have a permanent definition. If so, is there any parameter that tells OPENQUERY to do its best and try to make an intelligent assumption about the types of columns?

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 the following:

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

The reason for this is that when the stored procedure is executed on the linked server, the provider first tries to determine the shape of the resulting rowset. He does this by writing SET FMTONLY ON; , and then launches your statement. In a stored procedure that does not use temporary tables, this works great. The query parser basically performs a dry run, without actually receiving all the data, but only metadata (it seems to show an approximate execution plan).

The problem is that when a stored procedure uses temporary tables, it fails because the metadata of the temporary table does not exist: it cannot be collected using a meta-analysis that works for stored procedures that do not use temporary tables. Thus, because of this, it will be manually SET FMTONLY OFF; in a package that executes a stored procedure.

Remember that with this method, the stored procedure is executed twice . The first time to collect metadata (data is discarded), and the second time to actually return the data. If the called stored procedure is particularly expensive or has side effects, you may have to make a discount.

Finally, note that this trick does not work for every stored procedure. There are things stored procedures can do this, just throw a wrench in the works. I do not know all the possibilities, but one of them returns several sets of records.

In response to your update, that SET FMTONLY OFF does not work: can you change the structure of your SP to not use a temporary table, or use a permanent table with a session? Any of these options could do the job. In SQL Server 2012, you can also transfer data with table parameters .

You might like to read Erland Sommarskog How to Share Data Between Stored Procedures , as it can serve as a source of inspiration for your goal.

Answer 2

Adding " WITH RESULT SETS [NONE | UNDEFINED] " at the end of the EXEC call should fix this problem. http://technet.microsoft.com/en-us/library/ms188332.aspx

Answer 3

if the sp guest server does not have a result set (select query) at the beginning, the linked server could not parse the result set. used option:

 create procedure test as if 1=2 select a, b, c from table declare @variable varchar(10) ----...and rest of your procedure... --in the end select a, b, c from table