Roolback not functioning in OPENQUERY Linked Server call to ORACLE Stored Procedure

Question

Post reply

Roolback not functioning in OPENQUERY Linked Server call to ORACLE Stored Procedure

mhilligas

Ten Centuries

Points: 1354
More actions
January 21, 2010 at 2:46 pm

#229276

I am doing an BEGIN TRANSACTION
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 11 posts - 1 through 10 (of 10 total)

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

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

Please do not cross post ... use "BEGIN DISTRIBUTED TRANSACTION" and check BOL for further 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.

mhilligas Ten Centuries Points: 1354 More actions · Answer 2

Thanks for your response.....Sorry for the cross post.

According to BOL the transaction is automatically upgraded to a Distributed Transaction when using a Linked Server Call.

If I code a Distributed Transaction I get this error and have tried everything to no avail to stop it.

Msg 7391, Level 16, State 2, Line 1

The operation could not be performed because OLE DB provider "MSDAORA" for linked server "POMORA" was unable to begin a distributed transaction.

What do you think?

Michael Hilligas

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

mhilligas (1/22/2010)
What do you think?

I think you may be developing a resistance to read documentation 😀

Check if MSDTC is running on your SQL Server host; also check if your odbc or whatever provider you are using actually supports distributed transactions.

_____________________________________
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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Also check network connectivity. MSDTC works on the RPC port (135) and it must be reachable in order to make it work.

I don't know if this is the case when the linked server is other than SQL Server, but I would give it a look.

-- Gianluca Sartori

mhilligas Ten Centuries Points: 1354 More actions · Answer 5

WOW You assume to much. I am seeking help not abuse!

I have read everything available on the subject. So have the engineers on the ORACLE side.

Yes MSDTC is running and configured for network access.

I have not seen a difinative answer to the question: Does MSDAORA support Distributed Transactions.

Do you know if MSDAORA OLEDB Provider supports it?

I am doing many transactions with ORACLE on this project and this is the last step in making it complete.

Anything you would bring to the table would be helpful.

Thanks again for you response.

Michael Hilligas

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Yes, MSDAORA supports distributed transactions.

As you have checked all possible documentation available, I suppose you already have read this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106

If your Oracle is on Windows, take a look at this:

http://support.microsoft.com/?scid=kb;en-us;817064&x=18&y=14

-- Gianluca Sartori

mhilligas Ten Centuries Points: 1354 More actions · Answer 7

Thanks for the help.

I am looking into if the MtxOCI.dll file is loaded.

I am unfamiliar with this file.

All of my functions to ORACLE work very well as far as executing stored Procedures and views on ORACLE.

The ORACLE Server in on LINUX.

I am in the 11th hour on his and need it to work very soon.

Thanks again for the info

Michael Hilligas

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

I'm sorry, I'm never been there so in depth.

Hope somebody else can give it a look.

-- Gianluca Sartori

mhilligas Ten Centuries Points: 1354 More actions · Answer 9

I investigated the MtxOCI.dll path and found a document that pointed me to some registry settings.

When I looked they where using ORACLE 8 DLL references.

I changed them to the 10g dlls we are really using and i can now do Distributed Transactions.

MESSAGE 9 of this article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 led me to this.

http://msdn.microsoft.com/en-us/library/aa344212(VS.85).aspx

which showed me the registry settings.

THANK YOU ALL VERY MUCH.

YOU GUYS ROCK!!!!!

Michael Hilligas

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Glad you put it to work, congrats!

-- Gianluca Sartori