All geek questions in one place

Insert stored procedure results from a linked server

Can I paste the results of a remote stored procedure into a temporary table? for instance

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

Where tst is the stored procedure that returns identifiers.

If I run exec myself, it works fine

 EXEC [linkedserver].remoteDB.dbo.tst

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

“OLE DB provider“ SQLNCLI ”for linked server“ linkedserver ”returned message“ Partner Transaction Manager has disabled remote / network transaction support. ”Msg 7391, Level 16, State 2, Line 2 The operation could not be completed because the OLE DB Provider is“ SQLNCLI "for linked server, linkedserver could not start a distributed transaction."

SQL Server 2005 and the other 2008 are running on one machine, both working with the "Distributed Transaction Coordinator" service.

+6
source share
2 answers

It seems to me that remote transaction support has not been enabled properly.

You tried to follow the instructions here:

+3
source

I think the reason is because we are calling EXEC alone, which it does not call inside a transaction, so there is no problem. When we call INSERT EXEC, it is called inside txn, so the remote server must enable network txn support. But we can avoid this:

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

+1
source

Source: https://habr.com/ru/post/893872/

More articles:


All Articles