• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1391
  • Last Modified:

Problems with Distributed Transaction Coordinator (MSDTC)

After changing IP-addresses on a subnet, I can no longer run transactions between my two SQL servers. The first server (pluto) runs Win2008 with MS SQL 2008. The remote server (mars) runs WinXPpro with MS SQL 2000.

Mars runs a stored procedure on a regular basis. This script starts with BEGIN TRANSACTION, and runs some SELECT's before an UPDATE. This update fails with the following error message:
---
OLEDB provider "SQLNCLI10" for lined server "MARS" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 6
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MARS" was unable to begin a distributed transaction.
---

The script works perfectly if I remove "BEGIN TRANSACTION" (and commit/rollback), and some other simple testing indicate that the problem indeed is the transaction part.

Since I think the problem started after changing IP-addresses, I've been looking for connectivity problems, but everything seems OK. Maybe someone have some good tip where we might have specified a IP-address? :)

Already checked:
* Both computers can ping each other with both IP and FQDN (reported by SELECT @@SERVERNAME)

* DTC service is up and running
* No firewall between computers
* DTCPing (http://support.microsoft.com/kb/918331) tested positive from both servers
* Restarted both machines as well :)
* tried "SET REMOTE_PROC_TRANSACTIONS OFF"  and "SET XACT_ABORT ON" before "BEGIN TRANSACTION"

Any good ideas?
 
0
nebb-jsr
Asked:
nebb-jsr
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
try BEGIN DISTRIBUTED TRANSACTION  instead ofBegin tran
0
 
nebb-jsrAuthor Commented:
Same error message. As I mentioned, the script worked perfectly earlier, so the script should be fine..
0
 
liorfrCommented:
try this:
goto administrative tool -> component services
Expand component services -> computers -> My computer -> Distributed Transaction Coordinator
Right click Local DTC and choose properties
Goto security Tab
Check the following boxes:
Network DTC Access
Allow remote clients
Allow remote administration
Allow Inbound
Allow Outbound
No Authentication required
Enable XA transactions

Do this for BOTH computers!
0
 
nebb-jsrAuthor Commented:
Ah! That did the trick! For some reason, the authentication have been enabled. Disabled it, and voila! :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now