How to save result of executing command in link server

Meyssam Toluie 0
Viewed 11

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?

distributed-transactions tsql sql-server
1 Answers

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')
David Browne - Microsoft0
Related