I have 2 Windows servers EC2 located in different region. Both of them have public IP so they can communicate with each other.
In order to allow remote query, i created a linked server. Basically, they are in different region so i used their public IP as data source. 1st server IP: 13.xxx.xxx.xxx 2nd server IP: 52.xxx.xxx.xxx
I tested the connection and it was successful. Remote query also worked fine. For example, update table set number = 0 from [remote-server-IP].txtoc.dbo.xhead where code ='1234'
But if i use BEGIN TRANSACTION and COMMIT TRANSACTION like this BEGIN TRANSACTION update table set number = 0 from [remote-server-IP].txtoc.dbo.xhead where code ='1234' COMMIT TRANSACTION
i got error. OLE DB provider "SQLNCLI11" for linked server "linkedservername" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "linkedservername" was unable to begin a distributed transaction.
I tried many methods - Enable Distribute Transaction on both server from Component Service - Allow them work through Windows Firewall - Allow all traffic between them (setting in Security group and Windows Firewall)
But none of them worked.
Anyone can shed a light on this, please?