Inserting Results Of Stored Procedure From Linked Server

Question

Is it possible to insert the results of a remote stored procedure into a temp table? For example

CREATE TABLE #test(id INT)
INSERT INTO #test 
EXEC [linkedserver].remoteDB.dbo.tst
DROP TABLE #test

Where tst is a stored procedure that returns IDs.

If I run the exec on its own it works fine

EXEC [linkedserver].remoteDB.dbo.tst

However when I put it as part of an insert I get this error

"OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "linkedserver" was unable to begin a distributed transaction."

One machine is running SQL Server 2005 and the other 2008, both are running the "Distributed Transaction Coordinator" service.

Answer 1

It sounds to me like the support for remote transactions has not been properly enabled.

Have you tried following the instructions here:

• The partner transaction manager has disabled its support for remote/network transactions

Answer 2

I think the reason is when we call EXEC alone it's not called within a transaction, so no problem. When we call INSERT EXEC, it's called within a txn, so the remote server has to enable network txn support. But we can avoid doing that with this:

https://dba.stackexchange.com/questions/46541/how-to-insert-in-table-from-remote-stored-procedure-without-creating-a-distribut