Msg 7391 Unable to begin a distributed transaction
-
Hi,
I have two servers - On a daily basis Server A Executes a S. Proc on Server B which collects data (selects) from partictular tables and inserts into a local temp table on Server A.
Im getting 60% Success Rate The rest of the time returns the following:
Msg 7391, Level 16, State 2, Procedure procedurename, Line 20
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "servername" was unable to begin a distributed transaction.
Both Servers are Windows 2003 Sp1 & SQL 2000 sp4, although I have seen this problembetween SQL 2005 & SQL 2000 sp4
They are in the same Workgroup. They are set up as Linked Servers in SQL and
have entries in the Hosts file on each server.
I've ruled out -
The MSDTC Security Settings
Firewall
Its not a loopback query
In fact.. the following query fails intermittently:
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
SELECT *
FROM <server_name>.Pubs.dbo.Authors
WHERE au_lname = 'Ringer'
COMMIT
This issue is causing major problems, due to its nature and because its failure rate is high
Any help would be very much appreciated!!!!
-
You say you have ruled out MSDTC Security Settings, but I still think that is the problem. I turned on the top 6 checkboxes (Network DTC Access, Both Client Admin and all 3 Transaction Manager Comms). You may need to play with the TMC Auth buttons too. NOTE: you MUST REBOOT the machine after config changes. Restarting DTC is not sufficient.
Also see Microsoft KB 306212 for other possible causes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks for the reply,
The settings I have are for the MSDTC are as follows:
Network DTC Access
Allow Remote Clients Allow Remote Admin
Allow Inbound Allow Outbound
No Authentication Required
Enable XA Transactions
The settings have been there since the machine was built and has had a reboot.
Mostly these settings work - its just the intermittentness of it that I cannot suss.
I have previously followed the microsoft article you have suggested and I can rule out any of there suggestions. I strongly believe it is in a bug in windows server 2003 sp1 but I have no proof. Any further suggestions would be greatly appreciated, Im running out of ideas and it is causing problems.
-
You did not mention the Transaction Manager Communication options. Make sure all three options are turned on in that section of the dialog. Did you check there registry settings mentioned in the KB I posted? Is the service set to login as NT Authority\NetworkService?
As a work around, try using remote sproc calls. You will need to ensure RPC and RPC OUT is TRUE in the linked server configuration. Also, do you HAVE to use Dist Tran, especially since you are only doing a select??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
All options in the Transaction Manager Communication section are selected except: Enable TIP Transactions. RPC & RPC Out are both enabled in the linked servers and the MSDTC Service is logged in as NT Authority\NetworkService.
The SQL Service account also has sufficient permissions on the registry.
The SELECT Statement uses Dist Tran just to test the dist Tran between the servers. Its not the main code, too much to paste here
Sorry to be a pain, but I have been scratching my head on this for months now.
-
Hmm. Need to start reaching here.
Try enabling TIP. Reboot and test.
How did you validate no firewall problem?
Try specifying an alias for the linked server with specific TCP/IP address and set up linked server to that. Perhaps some wierd name resolution issue can be worked around by that kludge.
Try named pipes as above.
Do BOTH machines have the same DTC configurations and have both been rebooted since DTC changes were made?
Can you do dist tran in the reverse direction?
If these things don't work, I'm fresh out. I have had similar problems (one of them just yesterday actually) and what I have covered prior to this post worked. I DO know your frustrations though!! Biggest thing was that a reboot was required after any DTC config change it seemed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
cheers for the help, The machines have been tested without firewall. Also, I have read this in microsoft website:
Before the release of Windows XP and Windows Server 2003, the TIP service, by default, listened on a fixed port, which made the DTC vulnerable to attack on that port, introducing an unwanted security risk. In Windows XP and Windows Server 2003, the TIP service is turned off by default, helping reduce the risk of attack on the TIP port.
I cant think of a reason why this setting would help so I haven't in the past. The Servers are very much 'live' and would need a really good reason to re-boot making the task a tad harder.
I see the point with the linked server alias. That could be an Idea.
-
Hi guys!
Were you able to solve this problem? I'm using Windows XP SP2 and SQL Server 2000 and both are installed in one pc. Oracle8i is installed on a Unix box. I also tried the different suggestions in microsoft and other db forums but still the same error. I access the linked server thru a View Table. In Query Analyzer, performing a select in the view table works fine but when I access the view table in my application, the problem occurs. The weird thing is that the error only occurs on the first access then works on 2nd access. Any idea?
TIA!
-
The settings I have are for the MSDTC are as follows:
Network DTC Access
Allow Remote Clients
Allow Remote Admin
Allow Inbound
Allow Outbound
No Authentication Required
Enable XA Transactions
I have the same problem. But I don't have all these options on my "Security Configuration" tab. I have
Network DTC Access (checked)
-- Network Administration (checked)
-- Network Transactions (checked)
-- Network Clients (checked)
-- TIP (not checked)
XA Transactions (checked)
I can exec a query on my Server A that is linked to Server B and it works fine. When I wrap it in a "BEGIN DISTRIBUTED TRANSACTION" or "BEGIN TRANSACTION" then I get the error message. What's the real difference between doing it with the DISTRIBUTED keyword or not? Thanks for any ideas.
There is no "i" in team, but idiot has two. -
I have the same issue with an application that uses the JDBC driver to do an update on a database 2 by comparing two databases 1 & 2. Now, I grabbed the code from my application and tried to do the update then it errored out saying "MS SQL OLEDB could not begin Distributed Transaction' where as if I take out the subquery which is just a select statement it runs fine,also it actually uses the Linked Server fully qualified naming covention.
What has to be specified to make sure that UPDATE runs successfully atleast from my Workstation against two servers linked by a Linked server. I would appreciate an elaborate answer because I have browsed through all the KB articles on MS DTC issue ans also on error 7391.
Thanks!!
The_SQL_DBA
MCTS"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
-
I too am facing an error
"The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX" was unable to being a distributed transaction
NOTE: This problem is only getting reproduced on virtual machines
The distributed transaction ain't the loopback transaction
Tried solutions:
1) Did tried msdtc security settings
2) Did reboot afterwards
Any recommendations? 🙂
-
That should sort you out
-
Enable Distributed COM on this Computer
-
Follow these steps:
a. Click Start, and then click Run.
b. In the Run dialog box, type dcomcnfg.exe, and then click OK.
c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer.
d. Right-click My Computer, and then click Properties.
e. In the My Computer Properties dialog box, click Default Properties tab.
f. In the Default Properties dialog box, click to Enable Distributed COM on this Computer check box.
-
Paresh Randeria (1/28/2009)
Follow these steps:a. Click Start, and then click Run.
b. In the Run dialog box, type dcomcnfg.exe, and then click OK.
c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer.
d. Right-click My Computer, and then click Properties.
e. In the My Computer Properties dialog box, click Default Properties tab.
f. In the Default Properties dialog box, click to Enable Distributed COM on this Computer check box.
g. The problem for me was that “Network DTC Access” option was not enabled on the ISSQLDW host DCOM settings
(dcomcnfg.exe?Component Services?My Computer?Properties?MSDTC?Security Configuration).
Kindest Regards,R
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply