Professional Web Applications Themes

Transaction have not rolled back. - Microsoft SQL / MS SQL Server

Hi Bona, Transaction are not automatically rolled back, unless you have SET XACT_ABORT ON set first. If xact_abort is on any error (except certain compilation errors) will roll back the transaction, which will give you the behaviour you are looking for. SQL Server MVP Erland Sommarskog has 2 excellent articles about error handling on his website http://www.algonet.se/~sommar/ which explain the ins and outs of error handling in SQL, and also lists the exceptions that I have mentioned above. -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Bona" <co.nz> wrote in message news:0c4601c36706$e0add820$gbl... ...

  1. #1

    Default Re: Transaction have not rolled back.

    Hi Bona,

    Transaction are not automatically rolled back, unless you have SET
    XACT_ABORT ON set first. If xact_abort is on any error (except certain
    compilation errors) will roll back the transaction, which will give you the
    behaviour you are looking for.

    SQL Server MVP Erland Sommarskog has 2 excellent articles about error
    handling on his website http://www.algonet.se/~sommar/ which explain the ins
    and outs of error handling in SQL, and also lists the exceptions that I have
    mentioned above.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Bona" <co.nz> wrote in message
    news:0c4601c36706$e0add820$gbl... 


    Jacco Guest

  2. #2

    Default Re: Transaction have not rolled back.

    It is not enough to specify that you are working with a transaction. You
    also need to check if there is an error. If you find an error then you need
    to role back the transaction. Check this small script that demonstrates it:

    create table demo (i int not null primary key)
    go
    create procedure MyProc as
    begin tran
    --create the first error
    insert into demo (i) values (NULL)
    insert into demo (i) values (1)
    --Create the second error
    insert into demo (i) values (1)
    insert into demo (i) values (2)
    commit tran
    go

    exec MyProc
    select * from demo
    go
    drop table demo
    drop procedure MyProc

    If you'll run this script then you'll see that the stored procedure
    produced 2 errors, but inserted 2 records into the table. If the stored
    procedure to execute all the
    insert statements or no statements at all, then I need to modify the stored
    procedure. The stored procedure would look like that:

    create procedure MyProc as
    declare err int
    begin tran
    --create the first error
    insert into demo (i) values (NULL)
    SET Err = error
    IF err <> 0
    BEGIN
    rollback
    return
    end
    insert into demo (i) values (1)
    SET Err = error
    IF err <> 0
    BEGIN
    rollback
    return
    end
    --Create the second error
    insert into demo (i) values (1)
    SET Err = error
    IF err <> 0
    BEGIN
    rollback
    return
    end

    insert into demo (i) values (2)
    SET Err = error
    IF err <> 0
    BEGIN
    rollback
    return
    end

    commit tran


    Since SQL Server's error handling works with the variable error that gets
    reset after each statement, you need to check it's value after each DML
    operation. An alternative way is to use SET XACT_ABORT ON in the stored
    procedure which rollback the transaction in case of errors that were caused
    by the DML operations (you can find more about it in BOL).

    Adi

    "Bona" <co.nz> wrote in message
    news:0c4601c36706$e0add820$gbl... 


    Adi Guest

  3. #3

    Default Re: Transaction have not rolled back.

    Thanks, Adi.
    This is fully explained the errors which I had in my stored procedure!



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    bona Guest

Similar Threads

  1. [PHP-DEV] Beta 2 rolled
    By Edin Kadribasic in forum PHP Development
    Replies: 0
    Last Post: October 30th, 11:52 AM
  2. [PHP-DEV] Beta 2 rolled
    By Andi Gutmans in forum PHP Development
    Replies: 2
    Last Post: October 30th, 11:30 AM
  3. Arrow does not change to hand in IE or NS when rolled over image map
    By flash-photo webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 18th, 05:55 PM
  4. committed data being rolled back???
    By DA Morgan in forum Oracle Server
    Replies: 4
    Last Post: December 29th, 06:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139