Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12

    Unanswered: Linked 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:tartTransaction returned 0x8004d013: ISOLEVEL=4096].

    I try the Oracle OLE DB Provider without sucess.

    Can anyone help me ?

    Thank you,

    Aldair.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    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

  3. #3
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12
    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.

  4. #4
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12
    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.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    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

  6. #6
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12
    Hi.

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

    SYL.

    Aldair.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •