SQLServerWiki

“The Only Thing That Is Constant Is Change”

Unable to setup transactional replication with updatable subscriptions.

Posted by database-wiki on April 11, 2016

Symptoms

Problem:-
• We have 3 separate servers.
• one publisher and 2 subscribers.
• Only 1 database is replicated. size is 6.50MB
• We are replicating all tables.

Error message when we set up replication:-

TITLE: New Subscription Wizard
——————————

Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication.

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “REPLLINK” was unable to begin a distributed transaction.
Changed database context to ‘Enterprise Datastore’.
OLE DB provider “SQLNCLI” for linked server “REPLLINK” returned message “No transaction is active.”. (Microsoft SQL Server, Error: 7391)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer

Cause

When we run sp_link publication explicitly we get:-

sp_link_publication @publisher = ‘publisher name’, @publisher_db = ‘database name’, @publication = ‘publication name’, @security_mode = 1, @login =
‘null’, @password =’null’, @distributor = ‘distributor name’

Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “REPLLINK” was unable to begin a distributed transaction.

• Even when we change the security mode to 2, we still get same error.
• There seems to be issues with MSDTC.
• We checked and rechecked the MSDTC settings multiple times, but no luck.

Simple test:-
We ran the following on subscriber:-

begin distributed transaction
select * from [publisher].master.dbo.sysdatabases
commit transaction

Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider “SQLNCLI” for linked
server “ED6R65” was unable to begin a distributed transaction.

• We get the same error when we perform this test on the publisher connecting to subscriber.
• This simple test shows we have issues running a distributed transaction, which clearly indicates that MSDTC is not working properly.

Resolution

-Once we fixed MSDTC, we were successfully able to test distributed transactions and create the subscriptions successfully.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: