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

    Unanswered: maintaining atomicity

    Hello Friends,
    Iam new to this sql server arena. I have implemented a procedure which does a series of insert and update statements and all of this statements must be implemented all at once or none. But if I got error in some statements , the rest of the statements are been executed. Please suggest me a way or code snippet to achieve atomicity in a sqlserver procedure.

    regards,
    Ch.Praveen Kumar.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use transactions:

    begin tran

    insert ...

    if @@error<>0 begin
    rollback tran
    RAISERROR ...
    return
    end

    update ...

    if @@error<>0 begin
    rollback tran
    RAISERROR ...
    return
    end

    commit tran

  3. #3
    Join Date
    Nov 2003
    Posts
    11
    Thank You For Your Reply. I Will Implement It.

  4. #4
    Join Date
    Nov 2003
    Posts
    11
    Hi,
    I got a small doubt here. Whenever an error encounters, how to display the sqlserver error message rather than displaying our custom message with raiseerror. Is there any way to get the error message from sql server if an error encounters such as foriegn key violation. (invalid references to pk in mastertable)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem that I see is that errors come back to the client application in a "stream", much like TDS or STDOUT provide. Transact-SQL sees the most recent error, the client sees every error. Unfortunately, the formatted error message (including things like object names) is only passed to the client, I don't think that Transact-SQL can even access it (although ADO can).

    -PatP

Posting Permissions

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