I have a SQL2000 machine that has a linked server that runs SQL2005 (before this was a SQL2000)
When the systems were both SQL2000 from ServerA it was possible to run:
SELECT * FROM MYSVR.MYBD.DBO.MYTABLE
After the ServerB has been upgraded to SQl2005 the code above returns the error:
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].
I've checked that the MSDTC is running with NetworkService and that the DCOMCNFG has DTC allowed for incoming/outgoing connections on both computer and that is not requesting authentication.
If I run the query: