I am trying to implement Transactional Replication with Updatable Subscribers across two different Servers.
DB_P is the Publisher database on Server1.
DB_S1 is the Subscriber database on Server2.
DB_S2 is the Subscriber database on Server1.
DB_S3 is the Subscriber database on Server2.
SQLSERVER services are enabled as NETWORKSERVICE. MSDTC is enabled on both servers. Checked RPC call via DTCPing.
After setting up Distributor, Publisher and Subscriber, when I execute sp_link_publication statement on the Subscription database (DB_S2) which is on same Server as of Publisher database (DB_P), it works fine.
But when I execute sp_link_publication statement on the Subscription database (DB_S1 and DB_S3) exists on Server2 it gives me the following error:
OLE DB provider "SQLNCLI10" for linked server "REPLLINK_SERVER1\-1468339317_PEGASUS2_A-1570475579_DBPUP369101659_DB_S-1726189269" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "REPLLINK_SERVER1\-1468339317_PEGASUS2_A-1570475579_DBPUP369101659_DB_C-1726189269" was unable to begin a distributed transaction.