How to get resultset with stored procedure calls over two linked servers?
- by räph
I have problems filling a temporary table with the resultset from a procedure call on a linked server, in which again a procedure on another server is called.
I have a Stored Procedure sproc1 with the following code, which calls another procedure sproc2 on a linked server.
SET @sqlCommand = 'INSERT INTO #tblTemp ( ModuleID, ParamID) ' +
'( SELECT * FROM OPENQUERY(' + @targetServer + ', ' +
'''SET FMTONLY OFF; EXEC ' + @targetDB + '.usr.sproc2 ' + @param + ''' ) )'
exec ( @sqlCommand )
Now in the called sproc2 I again call a third procedure sproc3 on another linked server, which returns my resultset.
SET @sqlCommand = 'EXEC ' + @targetServer +'.database.usr.sproc3 ' + @param
exec ( @sqlCommand )
The whole thing doen't work, as I get an SQL error 7391
The operation could not be performed because OLE DB provider "%ls" for linked server "%ls" was unable to begin a distributed transaction.
I already checked the hints at this microsoft article, but without success.
But maybe, I can change the code in sproc1. Would there be some alternative to the temp table and the open query?
Just calling stored procedures from server A to B to C and returning the resultset is working (I do this often in the application). But this special case with the temp table and openquery doesn't work!
Or is it just not possible what I am trying to do? The microsft article states:
Check the object you refer on the destination server. If it is a view or a stored procedure, or causes an execution of a trigger, check whether it implicitly references another server. If so, the third server is the source of the problem. Run the query directly on the third server. If you cannot run the query directly on the third server, the problem is not actually with the linked server query. Resolve the underlying problem first.
Is this my case?
PS: I can't avoid the architecture with the three servers.
