Results 1 to 5 of 5
  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
    Jun 2003
    Location
    Ohio
    Posts
    149
    Are these 2 DBs on the same server or different servers?

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    The sqlnts1 and 2 are two servers.

    I would look forward ot hear from you.
    Thanks,
    Subha


    Originally posted by sbaru
    Are these 2 DBs on the same server or different servers?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What is the level of nested transactions you are going into ???
    find max value of @trancount at which the error
    "[OLE/DB provider returned message: Cannot start more transactions on this session.]"
    occurs
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    I have only one transaction on. I don't open multiple transactions.
    @trancount is only 1



    Originally posted by Enigma
    What is the level of nested transactions you are going into ???
    find max value of @trancount at which the error
    "[OLE/DB provider returned message: Cannot start more transactions on this session.]"
    occurs

Posting Permissions

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