Wednesday, January 9, 2013

Unable to begin a distributed transaction


ERROR Fix: Msg 7391, Level 16, State 2, Line 3

One of our application team members was trying to run Distributed Transaction query through linked server, he saying whenever 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 what he is getting while running Distributed transaction through linked server.
 
Query:
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [MSSQLSERVER].[DBA_DATABASE].[DBO].[user_Table]

Error:

OLE DB provider "SQLNCLI10" for linked server "MSSQLSERVER" 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 "MSSQLSERVER" was unable to begin a distributed transaction.

Work-around/mssqlfix:
Step 1:  Open Administrative tools from start menu.
Step 2:  Select Component Services from the Administrative tools.
Step 3:  Expand the Component Services.
Step 4:  Expand My Computer Under Component Services.
Step 5:  Under My Computer Expand Distributed Transaction Coordinator.
Step 6:  Right Click on Local DTC and select Properties.


Step 7:  Click on the Security Tab from the Local DTC Properties window.
Step 8:  Check Network DTC Access under Security Settings.
Step 9:  In the Transaction Manager Communication box, Check Allow Inbound and Allow Outbound.
Step 10: Check the required Authentication method as well as required.







Note: You can check here for the Microsoft's KB article about this error.
Distributed Transactions fail on Linked server, OLE DB provider "SQLNCLI10" for linked server, Linked Server Distributed Transaction error, Linked Server and Distributed Transactions‎, Distributed Transactions fail on Linked server , Msg 7391, Level 16, State 2, Line 12. The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server, Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could, Distributed Transactions fail on Linked server SQL Server 2012, Distributed Transactions fail on SQL Server 2008 R2, Distributed Transactions fail on SQL Server 2005, Distributed Transactions fail on server 2000,

0 comments:

Post a Comment