Rollback over linked server not working

Question

Rollback over linked server not working

mhilligas

Ten Centuries

Points: 1354
More actions
January 21, 2010 at 4:37 pm

#215942

I am doing an BEGIN TRANSACTION in Stored Procedure in SQL 2005
I am using an OPEN QUERY Call to an ORACLE Stored Procedure thru a Linked Server using MSDAORA OLEDB Driver
Functions work fine.
when attemting a ROLLBACK it is not rolling back on the ORACLE side
CAN YOU HELP?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

This is a distributed transaction so you have to initiate it by issuing "BEGIN DISTRIBUTED TRANSACTION" ... check BOL for details 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

rojen83 SSC Veteran Points: 281 More actions · Answer 2

Handle Transaction (Rollback/Commit) when applicable. Regards, ... If that's the case, temporary tables will not work. I have tried it for a couple cases

_____________

Download Windows 7 [/url]

rojen83 SSC Veteran Points: 281 More actions · Answer 3

When working with linked SQL Server tables, Office Access tries to create a ... against linked SQL Server tables can be updatable even if they do not select the .

_____________

Download Microsoft 2010 [/url]

mhilligas Ten Centuries Points: 1354 More actions · Answer 4

Thanks everyone for you comments.

I have solved the issue.

It had to do with the registry for MSDTC did not have the proper names of the ORACLE OLEDB Provider DLL's.

I believe the ORACLE 10g Client install should have updated these setting but did not.

Regards

Michael J. Hilligas

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

rojen83 (1/23/2010)
When working with linked SQL Server tables, Office Access tries to create a ... against linked SQL Server tables can be updatable even if they do not select the .

:blink:

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

mhilligas (1/25/2010)
Thanks everyone for you comments.
I have solved the issue.
It had to do with the registry for MSDTC did not have the proper names of the ORACLE OLEDB Provider DLL's.
I believe the ORACLE 10g Client install should have updated these setting but did not.
Regards
Michael J. Hilligas

Michael, Thank you for the feedback - sooner or later your post will benefit another person facing the same issue.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.