Wednesday, November 7, 2012

Resolved: Msg 7391 Unable To Begin a Distributed Transaction

Msg 7391, Level 16, State 2, Line 8

One of our application team members was trying to run Distributed Transaction query through linked server. He is saying whenever he is running the select query with Begin Distributed transaction it is throwing an error but the simple SELECT statement is working fine. So here no issue with linked server only issue with Distributed Transaction.

Below error message he is getting while running Distributed transaction through linked server.

Error Description:

OLE DB provider "SQLNCLI10" for linked server "EUSAAMW0024SQL" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

Workaround/Solution:

Step 1:  Select Administrative Tools from start menu and click Component Services 

Step 2:  After Clicking Component Services it will open the Component Services console like below.



Step 3:  Expand the Component Services inside that expand folder "Distributed Transaction Coordinator", now you can see Local DTC. 

Step 4:  Right click on the Local DTC Properties, it will open a new popup like below.



Step 5:  On the Local DTC Properties window, click on the Security Tab like below.



Step 6:  Check Network DTC Access under Security Settings like above.

Step 7:  In the Transaction Manager Communication box, Check Allow Inbound and Allow Outbound  and check the required Authentication method as per your requirement.

The operation could not be performed because the OLE DB provider 'SQLNCLI10' was unable to begin a distributed transaction, Unable to begin a distributed transaction, The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “(null)” was unable to begin a distributed transaction, Msg 7391, Level 16, State 2, Line 23, sql server 2012, sql server 2008, sql server 2000, sql server 2005,

0 comments:

Post a Comment