0

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

The error message I am getting is

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.

Is there anyway I can use #temp tables in an SP and call it from a view using OpenRowSet?

CREATE PROC TestSpWithTempTable
AS
Select distinct TxnType into #txnTypes from Transactions
-- lot of other stuffs going on here
select TxnType from #temp
GO

The view I created is:

CREATE VIEW SelectDataFromSP
    SELECT a.*
    FROM OPENROWSET('SQLNCLI', 'Server=(local);TRUSTED_CONNECTION=YES;',
         'exec DW.dbo.TestSpWithTempTable') AS a

The code that works but slow is

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
Faiz
  • 5,331
  • 10
  • 45
  • 57
  • can you access the user defined functions in that sql server instance?if yes then refer this link http://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view – Romil Kumar Jain May 12 '12 at 12:50
  • Yes, I can access the views.. – Faiz May 13 '12 at 14:51
  • I am asking about access to user defined functions, not to views. – Romil Kumar Jain May 13 '12 at 17:30
  • Apologies.. I meant views, functions and all other objects. I checked the links too but didn't find them helpful. The issue here is that SQL server doesn't like DML in remote procedure calls. I am looking for a work around on that.. – Faiz May 15 '12 at 03:53
  • Instead of trying to make it work with temp tables you could try to optimize the stored procedure to completely remove them. – Lieven Keersmaekers May 15 '12 at 07:00
  • I am not trying to find an alternate solution for my case but trying to investigate if this can be achieved keeping the #temp tables. The question here is the usage of DML in remote execution and how to get that done in SQL.. Thanks for your efforts Lieven but what I want to do is a bit different thing.. – Faiz May 16 '12 at 07:26

0 Answers0