?
Solved

Problems with Distributed Transaction Coordinator (MSDTC)

Posted on 2010-01-11
4
Medium Priority
?
1,372 Views
Last Modified: 2012-05-08
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
Comment
Question by:nebb-jsr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26287956
try BEGIN DISTRIBUTED TRANSACTION  instead ofBegin tran
0
 

Author Comment

by:nebb-jsr
ID: 26288044
Same error message. As I mentioned, the script worked perfectly earlier, so the script should be fine..
0
 
LVL 4

Accepted Solution

by:
liorfr earned 2000 total points
ID: 26288211
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
 

Author Comment

by:nebb-jsr
ID: 26293232
Ah! That did the trick! For some reason, the authentication have been enabled. Disabled it, and voila! :)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question