SQL 2000 OLEDB error 0x8004d00a in distributed transaction

I'm nearing my wits' end on this one. I've got two SQL Server 2000 instances (both SP 3a) each running on its own Windows Server 2003 R2 virtual machine, call them SQLA and SQLB. I've got SQLA set up as a linked server on SQLB. I've got a SQL login on SQLA dedicated to this linked server setup, I've configured the linked server to use that user's security context for unmapped logins, and have no login mappings defined.

I'm developing a stored procedure on SQLB which queries some tables in MSDB. I've verified that, from SQLB, I can select from all the tables involved in the stored procedure (with a simple SELECT * FROM SQLA.msdb.dbo.[table]).

When I try to execute the stored procedure, however, I get the following error:

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Msg 7391, Level 16, State 1, Procedure RestoreFromLastBackup, Line 55 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Things I've tried, without success:

All the recommendations in KBA 839279 - I've ensured MSDTC is running on both machines as Network Service. I've checked the security configuration for MSDTC on both machines, and confirmed that Network DTC access, Allow Inbound, Allow Outbound, and No Authentication Required are selected. I've confirmed that Windows Firewall isn't running on either machine. No dice on all fronts.

Any thoughts, input, advice, avenues of exploration, etc, very appreciated. Thanks!

more ▼

asked Feb 24, 2011 at 07:56 AM in Default

avatar image

Matt Cherwin
96 1 3 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Download DTCPing from MS and ensure there are no firewall issues between the two virtuals.

more ▼

answered Feb 24, 2011 at 08:08 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left

@normankelm provided the answer via Twitter:

"[S]etting XACT_ABORT in the query typically fixed problems like this."

Went ahead and added SET XACT_ABORT ON at the top of the stored procedure; this solved the issue.

more ▼

answered Feb 24, 2011 at 08:30 AM

avatar image

Matt Cherwin
96 1 3 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 24, 2011 at 07:56 AM

Seen: 3520 times

Last Updated: Feb 24, 2011 at 08:03 AM

Copyright 2018 Redgate Software. Privacy Policy