Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Please provide me with a solution

    I am developer who codes for both front end and back end.

    HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

    When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

    "[OLE/DB provider returned message: Cannot start more transactions on this session.]"

    To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

    I want to handle the errors using @@error programatically.

    Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

    "No transaction or savepoint of that name was found".

    To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

    This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

    Please help.

    Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.


    Begin distributed transaction abc / begin tran abc


    Update tables in sqlnts1

    set xact abort on

    update tables in sqlnts2

    set xact abort off

    Update tables in sqlnts1


    commit tran / rollback tran abc



    Thanks,
    Subha

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Posts
    5

    Unhappy Re: Please provide me with a solution

    I could not see any reply posted by enigma??????????????

    Originally posted by subkrish
    I am developer who codes for both front end and back end.

    HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

    When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

    "[OLE/DB provider returned message: Cannot start more transactions on this session.]"

    To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

    I want to handle the errors using @@error programatically.

    Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

    "No transaction or savepoint of that name was found".

    To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

    This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

    Please help.

    Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.


    Begin distributed transaction abc / begin tran abc


    Update tables in sqlnts1

    set xact abort on

    update tables in sqlnts2

    set xact abort off

    Update tables in sqlnts1


    commit tran / rollback tran abc



    Thanks,
    Subha

Posting Permissions

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