SQL Server & Windows updatable subscriptions Immediate updating enable Distributed Transaction Coordinator (MSDTC)

Original: SQL Server & Windows Updatable Subscriptions immediate update enable Distributed Transaction Coordinator (MSDTC)

In updatable subscriptions, in the subscription settings Update method, set queued updating to update now

Then update the table data in the subscription database with an error:

Msg 21079, Level 16, State 1, procedure Sp_getpublisherlink, line 52nd
There is no RPC security information for the publisher, or the information is not valid. Please specify this information with sp_link_publication.
Msg 20512, Level 16, State 1, Procedure Sp_msreplraiserror, line 8th
Updatable subscriptions: Rolling back a transaction.
Msg 3609, Level 16, State 1, line 1th
The transaction ends in the trigger. The batch has been aborted.

Review the stored procedure Sp_getpublisherlink , confirming the statement that throws the error RAISERROR (21079, 1) and RAISERROR (21079, 3), The main Table mssubscription_properties has no parameter information rows for the Distribution Agent. (You can look directly at the table mssubscription_properties or execute the stored procedure sp_helpsubscription_properties see if there is any information)

Follow the prompts to execute the stored procedure sp_link_publication Add related information:(Reference sp_link_publication)

--Set the configuration and security information used by synchronization triggers that update subscriptions immediately when connecting to the publisher sp_link_publication  @publisher = ' publisher ', @publisher_db = ' Publisher_ DB ', @publication = ' publication ', @security_mode =  2--2: Use existing user-defined linked server logins created through sp_link_publication

Execution failed with the following error message:

The OLE DB provider "SQLNCLI10" of the linked server "publisher" returned the message "The partner transaction manager has disabled its support for remote/network transactions." "。
Msg 7391, Level 16, State 2, line 1th
The operation could not be performed because the OLE DB provider "SQLNCLI10" of the linked server "publisher" could not start the distributed transaction.

Distributed transactions are not enabled, enabling is! Hey! The following are the same actions on each server:

1. Enable MSDTC-related servers

Control Panel -- "Management Tools -- "Service:

Distributed Transaction Coordinator

Remote Procedure Call (RPC)

Security Accounts Manager

2. Enable local Coordinator

Start -- "Management Tools -- "service component (or run: Dcomcnfg.exe )

Console Root node -- "Component Services -- "Computer -- "My Computer (right-click property)

Click "MSDTC" option, confirm tick " using the local coordinator "

Console Root node -- "Component Services -- "Computer -- "My Computer

(continue to click) Distributed Transaction coordinator-- "Local DTC (Right-click property)

3. MSDTC uses 135 port

Confirm Open port 135 or shut down the firewall or with services.msc Open service startup TCP/IP NetBIOS Helper Service

Restart Service Distributed Transaction Coordinator and put " Delayed Start " instead " automatically "

At this point in execution, the distributed transaction is ready to be completed and the Subscriber can be committed to the publisher synchronously.

--Set the configuration and security information used by synchronization triggers that update subscriptions immediately when connecting to the publisher sp_link_publication  @publisher = ' publisher ', @publisher_db = ' Publisher_ DB ', @publication = ' publication ', @security_mode =  2--2: Use existing user-defined linked server logins created through sp_link_publication

For more information:

sp_link_publication (Transact-SQL)

Managing Distributed Transactions

SQL Server & Windows updatable subscriptions Immediate updating enable Distributed Transaction Coordinator (MSDTC)