• Great question. Finally redeemed myself after missing a couple this week. Great way to start a Friday morning. 😀

    Everything is awesome!

  • Good question. Interesting that running the code one line at a time inserts one row and you get an error when executing COMMIT (No corresponding transaction exists). The error terminates the whole batch when run as a block.

    Have an excellent weekend everyone!!

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Koen Verbeeck (6/29/2012)

    Ron's series on transactions led me to believe it should be 2. 🙂

    Got it wrong, but definately learned something. Good question!

    (3 wrong in a row, this is not a good week)

    +1, except that I got only two wrong this week...

  • thanks for the question. cheers

    in case anyone is interested, here is another link on batches that explains things going on here:

  • (Bob Brown) (6/29/2012)

    I go this wrong because I answered 1. But that was because if you execute the select statement again by itself, you will get one row returned. The 'a' value is stored in the test table. So I'm not sure what this proves.

    Actually, check that again. I think you will find that the column name is 'a'. no rows are returned if you run the select after the error.

  • nice question

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Apparently it's a good thing my knowledge is limited and I didn't put too much thought into it, I got it right.

    I figured if any part of a transaction failed, the whole transaction would fail and roll back. Isn't that the reason for starting a transaction in the first place?

  • Not all types of error rollback the batch transaction.

    Try this out:

    --create table

    create table test(a int not null primary key)

    --execute following statements

    begin tran

    insert into test values (1)

    insert into test values (1)

    insert into test values (2)


    --error message


    (1 row(s) affected)

    Msg 2627, Level 14, State 1, Line 7

    Violation of PRIMARY KEY constraint 'PK__test__3BD0198E595B4002'. Cannot insert duplicate key in object 'dbo.test'. The duplicate key value is (1).

    The statement has been terminated.



    select * from test

    You'll get two rows. Only the second insert rolled back, not the full batch.

    neprosto posted a list that mentions what type of rollback happens for different types of errors.

  • i got this wrong, i read all your post but am not still sure i got it.

    Cheers! 🙂

  • ok i think i got it now :), it's because the whole thing is executed as a transaction and not as a statement. so the whole transaction was rolled back. 🙂

    Cheers! 🙂

  • At first glance I thought, hang on there is one row returned with the value 'a' but when I out put the results to text you can see



    (0 row(s) affected)

    Gppd question.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • if you don't have begin tran, the transaction execute.

  • Nice question on transaction behavior. Thanks!

  • SQL Server error handling is bizarre.

  • Great question! Thought it would be 2. But there is a explicit transaction beginning there.

    I just didn't know it would all be rolled back on error. Does the XACT_ABORT setting affect anything?

    Best regards,

    Andre Guerreiro Neto

    Database Analyst

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply