Inserting Results Of Stored Procedure From Linked Server

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.

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

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