server to Oracle

Hello Group:

I use a Linked Server to connect to a Oracle 8i base. Now I want to implement a distributed transaction using this Linked Server: Two-Phase Commit, but it doesn't work. I can send a "begin tran", followed by an "update" at the SQL 2000, but when I send a query to Linked Server, a simple "select", I get this error:
Server: Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I try the Oracle OLE DB Provider without sucess.

Can anyone help me ?

Thank you,

Aldair.Try sending simple queries to the oracle server & see what you get. Then gradually work up to nested transactions - keep it simple through each step of troubleshooting.

Also, do some research on the version of driver you are using - some are known to have bugs similar to what you describe.

Post back if problems,,

Cheers,

SG|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Hi

When you send a command to oracle, check the type of default locking that oracle uses - in SQL its read uncommitted for a transaction but oracle may be different. You can specify locking hints in your transactions BUT oracle may NOT recognise them, so you may need to customise the distributed transactions using oracle commands accordingly.

This type of problem requires slow, careful & methodical testing - dont rush it.

Post back if problems

Cheers

SG|||Hi.

Thank you for yours helps. I will try with care.

SYL.

Aldair.