Solved

MSDTC on <server\instance> is unavailable

Posted on 2009-05-18
18
2,481 Views
Last Modified: 2012-05-07
I am running a workstation running XP Pro (SP 3) and a server running Server 2003 Standard with SQL Server 2005.  I have a VB6 app on the workstation that connects to SQL to retrieve and modify data.  When the app modify's small amounts of data (10-20 characters in one or 2 columns) in certain tables, it works great.  There is one table that has a column with data about a page or 2 long (200-300 characters).  The app can read it, but when the data is modified and the user tries to save the changes back to the SQL DB, it throws the attached error:

Object Error 278533
MSDTC on server <servername\instance name> is unavaible.

I've googled this error and tried the following things with now improvment:
 - Made sure the Distributed Transaction Coordinator service was started on both the SQL server and workatation.
 - Went into Component Services and made sure the MSDTC was configured to allow network access, with NO AUTHENTICATION REQUIRED.
 - I verified the SQL login we were using in our connection string had read & write permissions.to the DB.
 - I rebooted both the server and workstation.

I don't know where to go next because I'm not sure what is causing this issue.
MSDTC-error.JPG
0
Comment
Question by:Uniqueinc
  • 10
  • 7
18 Comments
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
You mention checking properties of the service; but have you checked the service is running and set to Automatic ?
0
 

Author Comment

by:Uniqueinc
Comment Utility
Yes, the DTC (Distributed Transaction Coordinator) service is running on both machines and set to Automatic start.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Just running the MSDTC service wont help. Ensure that MSDTC was configured as per the Support site

http://support.microsoft.com/kb/839279
0
 

Author Comment

by:Uniqueinc
Comment Utility
http://support.microsoft.com/kb/839279 isone of the webpages I read when I Googled this issue.  I'm sorry I failed to mention it above, but I followed all of the instructions there, including uninstalling and re-installing the service.  I'm still getting the error even after following the instructions.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
You mentioned that you are able to do this

select * from linked_servername.dbname.schemaname.tablename

Can you confirm this one whether you are able to do this one

begin distributed tran
select * from linked_servername.dbname.schemaname.tablename
commit tran
0
 

Author Comment

by:Uniqueinc
Comment Utility
rrjegan17,
Unfortunately, I'm not a developer, just an installer/implementer.  Is that VB code you are asking me to run?  If yes, how do I run it?  Visual Studio is not on the workstation and I can't put it there (security reassons).
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Ok.. Try the query from SQL Server Management Studio aka SSMS.
It comes as part of your SQL Server Client Installation.
0
 

Author Comment

by:Uniqueinc
Comment Utility
It works fine when I run it on the server.  I just can't run SQL Mgmt studio from the workstation (again, my customer's security restrictions).  I have an ASP.net web service/app running on another machine that connects to this same DB using the same connection string/credentials and it doesn't have any trouble either.
0
 

Author Comment

by:Uniqueinc
Comment Utility
I'm, sorry.  Your query did error when I ran it in SQL Mgmt Studio.  It gave the same error.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
If that query failed, Then MSDTC is not properly configured for sure.

Forgot to ask you a question earlier itself:

What Service Pack is running in Your SQL Server?

Because a fix was applied in SQL Server 2005 SP2 wrt Linked Server.
Hence applying at least SP2 or SP3 would help you to fix this issue too.
0
 

Author Comment

by:Uniqueinc
Comment Utility
I am running version 9.00.4035.00  Enterprise Edition (64-bit) SP3.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
As I mentioned earlier, MSDTC is not properly configured for sure.

Have you disabled Windows Firewall in both the machines.
Otherwise you have to exclude MSDTC.exe in system32 along with TCP ports 135 and dynamic ports from 5100 - 5200

The query with Begin distributed transaction should work if MSDTC is enabled properly.
0
 

Author Comment

by:Uniqueinc
Comment Utility
Windows Firewall is disabled and the service is not running on both machines.  The firewall admin has opened those ports too.
My coworker just found a MS Hotfix (Windows Server 2003 Post-Service Pack 2 COM+ 1.5 Hotfix Rollup Package 12).  He's downloading it so that we can see if it helps.
0
 

Author Comment

by:Uniqueinc
Comment Utility
Ok.  The hotfix didn't help.  We still got the exact same message as before:
MSDTC on server <servername\instance name> is unavaible.
Here is what the Application event log said when I reran the the query from above:
Event ID: 8547
Resource Manager Creation failed: 0x8004d01c
(XACT_E_CONNECTION_DOWN)
Does this help narrow things down at all?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
Check whether any of the Cause mentioned here is applicable for you:

http://support.microsoft.com/kb/924151

And to fix the XACT_E_CONNECTION_DOWN issue, another hotfix is available ( hope this one helps us now)

http://support.microsoft.com/kb/897253

And this one's for your Windows Server 2003 machine, better to install SP2 because it would have fixed more issues.
0
 

Author Comment

by:Uniqueinc
Comment Utility
We are running a Veritas Failover Cluster.  The second article may be along the right track.  On Monday, (due to several German holidays this week) we are going to try to replicate this issue on another cluster (non-production, in a lab) that doesn't have all the security protocols as the production stack.  I'll let you know how it goes.  Thanks.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Let me know if you need any further inputs too.
Hope this helps you out to resolve this issue.
0
 

Author Closing Comment

by:Uniqueinc
Comment Utility
The MSDTC service wasn't configured properly as a cluster resource in the Veritas Cluster.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Learn about cloud computing and its benefits for small business owners.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now