August 31, 2017 at 8:26 am
I am getting the following error when adding a new subscription.
.OLE DB provider "SQLNCLI11" for linked server "REPLLINK_RIPNCDB1-268974458_PNC60785638_PUBPNC_COM-1026461326_PNC60785638" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 7
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "REPLLINK_RIPNCDB1-268974458_PNC60785638_PUBPNC_COM-1026461326_PNC60785638" was unable to begin a distributed transaction.
I have verified DTC settings (Allow network clients, allow inbound allow outbound, No authentication required, enable xa, enable sna) linked servers exist on both servers pointing to each other, repl-distributor exists on both servers. When I run the script manually I get a login failed on sp sp_link_publisher. I verified connectivity of the login in question and it has dbo rights to all necessary databases. When I check the security of the linked server above and verify the password I get message Access to remote server is denied because no login mappings exist but the login is on both servers with same password.
What am I missing.
September 1, 2017 at 3:22 am
Try to test DTC first by running some simple selects and using BEGIN DISTRIBUTED TRANSACTION:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql
You may need to modify the linked server to DATA ACcess and provide Security details for user in the linked server that you will be testing.
Also could you tell more about your configuration? Local or remote distributor? When u creating subscriptions are u using account that have sysadmin permissions on Publisher and Distributor (if u using remote distributor)?
What login subscription will be using to connect to subscriber? (windows or SQL Auth?)
BartL
Replication Blog
September 1, 2017 at 10:43 am
When I try the distributed transaction I get error
Msg 3910, Level 16, State 2, Line 3
Transaction context in use by another session. I tried killing some spids and restarting service to no avail.
The distributor is local and the login I am using is sysadmin on all servers
I am connecting to subscriber via SQL login.
My linked servers have data access of true.
A linked server was created as part of the process and added an entry for repllinkproxy.
September 5, 2017 at 4:51 pm
jamesforest - Friday, September 1, 2017 10:43 AMWhen I try the distributed transaction I get errorMsg 3910, Level 16, State 2, Line 3
Transaction context in use by another session. I tried killing some spids and restarting service to no avail.
The distributor is local and the login I am using is sysadmin on all servers
I am connecting to subscriber via SQL login.
My linked servers have data access of true.
A linked server was created as part of the process and added an entry for repllinkproxy.
MSDTC can lose it's marbles sometimes due to some unknown reason.
If it's still an issue for you and you're sure the settings are correct, I'd probably try an uninstall and reinstall of MSDTC
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply