Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

This error comes when you are trying execute distributed transaction via linked server. Personally I don’t prefer linked server and would always go for other alternatives like SSIS package or something else but one of my friend had a situation where he must had to setup linked server to make distributed transaction. Security wasn’t concern for him as his network was just an intranet and under hardware as well as software firewall so I have helped him to setup distributed transaction for linked server so that he can fix the error:

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

For example we have two server named “SATURN” and “MARS” accordingly. We have set link server of MARS in SATURN so that I can access database of MARS from SATURN. I wanted to execute one SP from one of the database of MARS from SATURN and it was working fine after setting up linked server but when I tried to take output of this SP into one temp table, it started popping up the error given above.

Let us try to resolve this error.

First I will connect to first server which is SATURN in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab

134

Select “Network DTC Access” checkbox and also select “Allow Outbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Once SATURN is configured, let us do the same in MARS with minor change:

Now, I will connect to second server which is MARS in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab .

136

Select “Network DTC Access” checkbox and also select “Allow Inbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Now, you will be able to execute distributed transaction via linked server.

I have some more article on the subject of Linked Server, have a look at it, if you are interested on this subject.

Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)

Linked Server is not configured for data access Error: 7411 in SQL Server 2005 (Click Here)

Linked Server in SQL Server 2005 from ACCESS 2007 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

2 thoughts on “Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

  1. Pingback: Backup Linked Server script in SQL Server by SSIS

Comments are closed.