sql-servert-sqldistributed-transactions

How to save result of executing command in link server

I need to execute same command in different server using linked-server.

INSERT #Result
EXEC ('command') AT @linked_server

Because Insert statement opens an Implicit transaction and I can not enable DTC(Distributed transaction control) the following error will occur:

The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "RM-BIEDW" was unable to begin a distributed transaction.

Is there any solution other than enabling DTC?

Solution

  • Turn of 'remote proc transaction promotion', eg

    EXEC master.dbo.sp_serveroption @server=N'linked_server', @optname=N'remote proc transaction promotion', @optvalue=N'false'

    Or use OPENQUERY

    insert #result 
select * from openquery(linked_server, 'command')