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

Harding

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.

Glioma

Sorry for the l-o-o-o-ong delay in responding. Didn't notice the notice. I get the same error when using the SET FMTONLY OFF approach. – Harding

I've always gotten around this by using a table variable (ugh). I hate doing that but it works. – Sanjak

@Gizmo The problem with table variables is that they don't have statistics created for them, like temp tables do... – Glioma

@ErikE...there are many problems with table variables and they should never be used unless there isn't another option. In this case, it was the only way to make it work in 2012 (hence the 'Ugh'). IMHO the only time table variables should be used are 1) when at temp object (table) contents need to survive a rollback 2) when there are problems with recompiles and 3) when all else fails. – Sanjak

Answer 2

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

Biometrics

I get this error: Cannot process the object "exec testproc WITH RESULT SETS NONE". The OLE DB provider "SQLNCLI11" for linked server "FOO\SQL2012" indicates that either the object has no columns or the current user does not have permissions on that object. – Harding

@Harding You may get that error when using WITH RESULT SETS NONE, if there are in fact results. Define the results that you get in the RESULT SETS, and make sure that the remote sPROC has SET NOCOUNT ON at the start and you should be fine, without even having to use SET FMTONLY OFF; The error you describe is caused by trying to select from a statement that you have specified has no results. – Mut

Answer 3

If the remote procedure returns a result set, it is essential to start the procedure with SET NOCOUNT ON, otherwise the first result set is the number of rows without any metadata and the error is always generated.

One can then define the result set using WITH RESULT SETS((field1 type1, field2 type2...)) at the end of the EXEC statement.

(This does not directly answer the question, but I had the same error because my stored procedure created a temporary table to store intermediate results.)

Humidor

Answer 4

if the guest server sp does not have a result set (a select query) in the begining, linked server could not parse the result set. the option i use is the following:

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

Pearliepearline

I don't understand the purpose of the first if-test: if 1=2 (i.e. false). Is the select statement executed anyway? Is the provider looking for an actual resultset, or looking for a SQL statement that would produce a resultset? – Harding