MSDTC Problem

Hi,

I recently moved & upgraded a database from 7.0 to 2000 that makes use of linked servers. After upgrading, a number of queries started failing with the error below. These linked servers with this problem either had a stopped MSDTC or were behind a firewall. I've read the articles on how to configure MSDTC to work through a firewall, but before I do this I'm wondering why this is even happening. There were no problems when the database was at SQL 7.0. Does anyone know why sql 2000 would need to use MSDTC, but SQL7.0 would not? I don't need to use a distributed transaction, so ideally I'd like to somehow tell SQL not to use MSDTC at all. Any ideas?

Thanks

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[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].Thanks in advance for any help you can offer.

I have same problem as cpyne.

I recently upgraded a SQL server 7.0 box to SQL server 2000.
(Standard edition)

I had several stored procs selecting data from linked servers and inserting into local tables.

They all ran fine for the last 2 years but after the upgrade they all fail.
Linked servers include SQL Server, ORACLE and INFORMIX databases.

Error report indicates that MSDTC is now unable to begin distributed transaction.

Anybody know why MSDTC is suddenly failing after I switched from version 7 to 2000?|||On most of our servers that had this problem, MSDTC was stopped on the remote server. Starting it and setting it to automatic fixed the issue. There were a few queries that didn't even appear to be distributed transactions. They were just local tables with joins to linked servers. I spoke with MS about this and they said that SQL 2000 makes more use for MSDTC in places where SQL 7.0 didn't. The only servers where we had difficulty getting this working were ones where there was a firewall between the master server and the linked ones. In the end we dicided to chang the logic of our application, but MS suggested some articles that listed which ports needed to be opened. I don't have them handy, but if you have this problem, search on Firewall and MSDTC.

Charlie

MSDTC on server <Server-Name> is unavailable.

I got this error after I put an update trigger on a table which resides
in Database A and queries a table contained in Database B via a
linkserver. I have looked at the server and have bounced both the SQL
Server service and the DTC service and still it says that it can't find
it. Since this is a production server, resetting it is kind of out of
the question (but I'll do the old Microsoft fix if I have to). I was
just curious if anyone else has had this problem?Topper (Kinky.Taylor@.gmail.com) writes:
> I got this error after I put an update trigger on a table which resides
> in Database A and queries a table contained in Database B via a
> linkserver. I have looked at the server and have bounced both the SQL
> Server service and the DTC service and still it says that it can't find
> it. Since this is a production server, resetting it is kind of out of
> the question (but I'll do the old Microsoft fix if I have to). I was
> just curious if anyone else has had this problem?
Firewalls?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try the following from the command prompt:
msdtc -uninstall
then...
msdtc -install
This will reset the msdtc instance on the server.
Probably will require a reboot, but this has worked for me in the past.
Good Luck...John|||This is how I got into this predicament, but I'll try a reboot later
on.
Thanks