title: How to call SPs with #temp using OpenRowSet in a View?
body: <p>I am trying to run a stored procedure from an application which supports connecting to only tables and views. My work around was to use a view which gets results from SP via Openrowset(). Turns out that the SP is using #temp tables to store intermediate results and this is a DDL operation which seems to be not supported for distributed queries. I can replace #temp with @temp table variables but it slowing down the whole code drastically (I was using bulk insert (select * into #temp from t1) to speed up things).</p>
  
  <p>The error message I am getting is </p>
  
  <blockquote>
    <p>Cannot process the object "exec
    DW.dbo.TestSpWithTempTable".
    The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates
    that either the object has no columns or the current user does not
    have permissions on that object.</p>
  </blockquote>
  
  <p><strong>Is there anyway I can use #temp tables in an SP and call it from a view using OpenRowSet?</strong></p>
  
  <pre><code>CREATE PROC TestSpWithTempTable
  AS
  Select distinct TxnType into #txnTypes from Transactions
  -- lot of other stuffs going on here
  select TxnType from #temp
  GO
  </code></pre>
  
  <p>The view I created is:</p>
  
  <pre><code>CREATE VIEW SelectDataFromSP
      SELECT a.*
      FROM OPENROWSET('SQLNCLI', 'Server=(local);TRUSTED_CONNECTION=YES;',
           'exec DW.dbo.TestSpWithTempTable') AS a
  </code></pre>
  
  <p>The code that works but slow is</p>
  
  <pre><code>CREATE PROC TestSpWithTempTable
  AS
  declare @TxnTypes table(TxnType varchar(100))    
  insert into @TxnTypes
  Select distinct TxnType from Transactions
  -- lot of other stuffs going on here
  select TxnType from @TxnTypes
  GO
  </code></pre>