SQL2000 linked to SQL2005 doesn’t allow TRAN selects
Hi all,
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:
Software/Hardware used:
BEGIN TRAN SELECT * FROM MYSVR.MYBD.DBO.MYTABLE COMMIT TRANAfter 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:
SELECT * FROM MYSVR.MYBD.DBO.MYTABLEwithout the TRAN part this works fine.
Software/Hardware used:
ASKED:
September 22, 2008 1:53 PM
UPDATED:
September 30, 2008 10:33 AM
Answer Wiki:
Check out this blog post on <a href="http://itknowledgeexchange.techtarget.com/sql-server/how-to-configure-dtc-on-windows-2003/">configuring MS DTC</a>. If it isn't configured exactly as shown then it won't work correctly.
If it still doesn't work please post any error messages from the Application and System logs from the MSDTC service.
All make sure that you have enabled network DTC from the Add/Remove Programs icon in the control panel.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi MrDenny,
as in my question that was already done and didn’t change; anyway the issue seems to be related to a different thing.
Theres SERVER1 (sql2005) that has a linked server to a local OLE DB compatible database (INSQL) so from remote SQL2000 on SERVER2 they cannot query the linked server via linked server
E.g S1 –>(LINKED) S2 –>(LINKED INTERNALLY TO INSQL OLE DB)
The solution I’ve provided is to have a batch on S2 that dumps the data from INSQL to a real SQL so S1 can query that DB without passing through the whole “linking path”