How to insert into a remote table using a linked server with a transaction?

Question

My linked server is configured correctly, I can execute the request below.

INSERT INTO [RemoteServer]. [Table] SELECT * FROM [LocalServer]. [Table]

However, when I do the same inside a transaction

START TRAN INSERT INTO [RemoteServer]. [Table] SELECT * FROM [LocalServer]. [Table] COMMIT TRAN

I get errors like

The OLE DB provider "SQLNCLI" for the linked server "66.70.123.202" returned the message "Operation is not active." Msg 7391, Level 16, State 2, Line 3 The operation could not be completed because the OLE DB provider "SQLNCLI" for the linked server "66.70.123.202" could not start a distributed transaction.

Answer 1

Verify that the MS DTC service is running on both servers and it is configured correctly to allow common requests.