SQL2000 linked to SQL2005 doesn’t allow TRAN selects

Tags:
Microsoft SQL Server 2000
SQL Server 2005
SQL Server errors
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:
BEGIN TRAN
SELECT * FROM MYSVR.MYBD.DBO.MYTABLE
COMMIT TRAN
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:
SELECT * FROM MYSVR.MYBD.DBO.MYTABLE
without the TRAN part this works fine.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • alessandro.panzetta
    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" :)
    9,725 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following