title: Using OPENQUERY (exec stored procedure) to create new temporary table fails with error 11526
body: <p>I have SQL Server 2012 full version installed on my development PC. </p>
  
  <p>I am trying to follow the examples <a href="https://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure">here</a>, 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).</p>
  
  <p>To test if the plumbing is working, I issue this query:</p>
  
  <pre><code> SELECT * FROM OPENQUERY("FOO\SQL2012", 'exec mySchema.myStoredProc')
  </code></pre>
  
  <p>But I'm getting this error from that simple test-the-plumbing select query:</p>
  
  <blockquote>
    <p><em>Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1<br>
    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.</em></p>
  </blockquote>
  
  <p>If I understand the error correctly, OPENQUERY depends upon the server being able to extract the column datatypes from the <em>persistent</em> definition in the database, and the temporary table instantiated in my stored proc, being ephemeral, lacks a persistent definition. If that is the case, <strong>is there any setting that tells OPENQUERY to do the best it can and try to make an intelligent guess at the column datatypes?</strong></p>
  
  <p>Here's the dummy SP I'm testing with:</p>
  
  <pre><code>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
  </code></pre>