All,
I am experiencing an error using MSDTC with SQL Server 2000 on Windows 2003
server. The code in question is a stored procedure that does a query within
a transaction using linked servers. The code worked perfectly well on NT 4
w/ SQL Server 2000. Running the same code on Windows 2003 produces the
following error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
In the past, the only time I've seen this error is when the MSDTC service
was not running on one of the two servers involved in the query. Both
servers have multiple NICs. Running the DTCPING utility, the RPC request was
failing. I then found another utility called DTCTester, which supposedly
returns more accurate error messages.
Among the possible causes are a) a firewall problem, b) a DNS or WINS
problem, or c) a network problem due to multiple NICs. There is no firewall
between the servers in question, and DNS works (each server can ping the
other, and each can be accessed using NET USE to mount a drive). WINS is not
used in this configuration. Finally, the binding order of the NICs is such
that the one enabled is the first in the list.
After disabling all the NICs except for those used for communication between
the 2 servers in question, DTCPing reports success, while DTCTester still
returns the same generic error message. I have also verified that the login
account used for the MSDTC service is correct.
Any advice as to what may be causing this would be greatly appreciated.
Thanks in advance for any advice.
-Gary