Oracle Update Stored Procedure - cannot ROLLBACK in a distributed

[Guest]

I have tested a procedure in TOAD and it functions as expected, meaning for
this particular transaction it returns and error message and performs a
rollback within the procedure.

When I call this stored procedure from within ASP.Net with the same data,
The procedure returns an error which is caught by my try-catch block and it
provides the following message...""Detail Error Message: ORA-02074: cannot
ROLLBACK in a distributed transaction\nORA-06512: at
\"PRG_OPS.TST_PRG_ADMIN_UPDATES\", line 424\nORA-06512: at line 1"". I
expected it to terminate normally and that I would be able to read my return
parameters to determine that the procedure had failed and I would be able to
retrieve my error message.

Does this message mean that I must control commits and rollbacks in my
program with a "Begin Transaction" type of logic? I can not issue a rollback
within my stored procedure? If so, does this mean I have to take the
rollbacks out of my stored procedures?

 

[Eric]

Are you familiar with distributed transactions? You've apparently
updated data in linked database instances. The owner session is the one
that started the transaction. That is where the distributed transaction
is controlled. A non-controlling session can't do commit or rollback.

You certainly can control transactions in stored procs, but you need to
be careful about it when using dblinks. A dblink spawns a new session in
the other db, and this is why you have trouble.

The best fix is to avoid updates across dblinks. If this isn't possible,
then consider the use of autonomous transactions.

You won't solve the problem by handling transactions using ADO.NET.

Eric

 

[angelsbadillos[ms]]

This is an Oracle client 8 bug. You need to download the SP1 of the 1.1
framework or contact PSS directly and request the hotfix by name
Q830173.
http://support.microsoft.com/default.aspx?scid=/support/contact/default.asp

Please note that it is _not enough_ to get the patched framework, you
need to add a special connection string keyword to your code (sorry I
can't remember exactly, please ask pss)

The problem is that with
version 1.1 of our oracle provider we added support for distributed
transactions. In order for an oracle connection to be able to enlist in
a
distributed transaction we need to set certain properties _before_
opening
the connection. Unfortunately there is a bug in Oracle 8x server where
once
this properties are set it treats the connection as enlisted. The only
workaround that we have is to make sure that the properties are not set
by
adding a new connection string keyword, make sure you ask PSS what it
is, I
believe it is something like "Workaround for Oracle Bug xxxx=true".


Hope this helps,
Angel
This post is provided "AS IS" and confers no rights.

 

[Guest]

:

Hi,

I'm having the same problem, but actually ran into trouble with getting the
HotFix.

This is an Oracle client 8 bug. You need to download the SP1 of the 1.1
framework or contact PSS directly and request the hotfix by name
Q830173.

Does Q830173 refer to a Knowledge Base article number? That article doesn't
mention this problem at all. Do you still think this is the same case,
though? I did call the local Microsoft technical support phone, but they
couldn't find the fix you mentioned either.

I do have SP1 for Framework 1.1 so I could basically try just addin the
connection string keyword. But I'm not sure if the support people would be
able to help me with the special connection string keyword if they couldn't
find the fix either..

Any ideas? All help is appreciated!

Thanks,
Jarno

 

[Craig]

I'm having the same problem...

We are using Oracle 8.1.7.3.

* I am using the ADO.NET Oracle Client
* I have installed Framework v1.1 SP1
* I have added enlist=false to the connection string

When I call my Oracle stored procedure and attempt a rollback I get the
error:

ORA-02074: cannot ROLLBACK in a distributed transaction

I would prefer not to use transactions at all, I've spent a lot of time
researching this, and found lots of half information and leads that go no
where.

I have tried fixes that suggest:
* Adding enlist=false to the connection string
* Installing Framework v1.1 SP1

I have not yet resorted to rolling back from the command object or using
OleDb and putting distribtx=0 on the connections string.


Please help,
Craig