Transaction

  • bayazidahmed

    Old Hand

    Points: 364

    Comments posted to this topic are about the item Transaction

  • Ron McCullough

    SSC Guru

    Points: 63877

    oops

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • M&M

    SSC-Insane

    Points: 21699

    Got this wrong, good question

    M&M

  • Konstantin Reu

    Hall of Fame

    Points: 3149

    Good question. I got wrong too... it is not obvios, and as a see termination of batch depend on not only of severity level.

    I've found link http://msdn.microsoft.com/en-us/library/ms188792(v=sql.105).aspx where at the end is placed intrested post with link. post very like QOD:)

    maybe has http://www.sommarskog.se/error-handling-I.html#statementbatch still applyed to SQL2k8 ? then

    Statement-termination and Batch-abortion

    These two groups comprise regular run-time errors, such as duplicates in unique indexes, running out of disk space etc. As I have already have discussed, which error that causes which action is not always easy to predict beforehand. This table lists some common errors, and whether they abort the current statement or the entire batch.

    Error Aborts

    Duplicate primary key. Statement

    NOT NULL violation. Statement

    Violation of CHECK or FOREIGN KEY constraint. Statement

    Most conversion errors, for instance conversion of non-numeric string to a numeric value. BATCH

    Attempt to execute non-existing stored procedure. Statement

    Missing or superfluous parameter to stored procedure to a procedure with parameters. Statement

    Superfluous parameter to a parameterless stored procedure. BATCH

    Exceeding the maximum nesting-level of stored procedures, triggers and functions. BATCH

    Being selected as a deadlock victim. BATCH

    Permission denied to table or stored procedure. Statement

    ROLLBACK or COMMIT without any active transaction. Statement

    Mismatch in number of columns in INSERT-EXEC. BATCH

    Declaration of an existing cursor Statement

    Column mismatch between cursor declaration and FETCH statement. Statement.

    Running out of space for data file or transaction log. BATCH

    MS SQL 2008 MCITP x 3
    MS SQL 2012 MCSE x 2

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Skanker

    Hall of Fame

    Points: 3059

    Got it wrong - confused myself with another question so learnt something today.

    Oh well hopefully get to see the Olympic torch later today so my day will get better. 😀

  • call.copse

    SSCoach

    Points: 17242

    Yeah, I was a little mislead by Ron's fine series on transactions - fortunately I did remember the message of checking the severity and got it right, after lousing up the last two.

  • (Bob Brown)

    SSCrazy

    Points: 2705

    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.

  • george sibbald

    SSC Guru

    Points: 104200

    Robert.Brown 26968 (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.

    you sure robert? 'a' is the name of the column.

    ---------------------------------------------------------------------

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Good question! Got I wrong, but I'm glad to see that I'm not the only one who hasn't memorized what exceptions that leads to aborting a batch.

  • (Bob Brown)

    SSCrazy

    Points: 2705

    I stand corrected. I am seeing the column name. Thanks.

  • tim.bearne

    Ten Centuries

    Points: 1135

    Interesting question but why doesn’t SQL Server tell us what it’s done? For example “(1 row(s) rolled back, batch terminated)”.

  • Ron McCullough

    SSC Guru

    Points: 63877

    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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thomas Abraham

    SSChampion

    Points: 10761

    I had the advantage of being so old, that Ron's series was a distant, fuzzy memory. So, I got it right.

    However, when running the script as provided, I got this error:

    [font="Courier New"]..Net SqlClient Data Provider: Msg 213, Level 16, State 1, Line 6

    Column name or number of supplied values does not match table definition.[/font]

    After a bit of head scratching, I realized it was because I already had a table "test" in my practice DB, which had different columns, of course. Didn't get an error message about trying to create a table that already existed though. However, if you run just the create statement in it's own session, you get:

    [font="Courier New"].Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 2

    There is already an object named 'test' in the database.[/font]

    Is the error for column name suppressing the error for attempting to create an already existing table?

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • SQL Surfer '66

    SSCertifiable

    Points: 5159

    Really good one. Fell right into the trap. 😀

Viewing 15 posts - 1 through 15 (of 37 total)

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