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?
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].
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.