Post reply

Transaction

  • June 28, 2012 at 8:08 pm

    #271915

    Comments posted to this topic are about the item Transaction

  • June 28, 2012 at 8:14 pm

    #1506918

    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]

  • June 29, 2012 at 12:00 am

    #1506945

    Got this wrong, good question

    M&M

  • June 29, 2012 at 12:30 am

    #1506956

    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.

    ErrorAborts

    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 cursorStatement

    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

  • June 29, 2012 at 12:30 am

    #1506957

    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

  • June 29, 2012 at 1:46 am

    #1506975

    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. 😀

  • June 29, 2012 at 3:24 am

    #1507006

    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.

  • June 29, 2012 at 4:28 am

    #1507029

    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.

  • June 29, 2012 at 4:53 am

    #1507041

    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.

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

  • June 29, 2012 at 5:11 am

    #1507046

    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.

  • June 29, 2012 at 5:17 am

    #1507049

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

  • June 29, 2012 at 5:26 am

    #1507053

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

  • June 29, 2012 at 5:34 am

    #1507056

    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]

  • June 29, 2012 at 5:52 am

    #1507065

    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

  • June 29, 2012 at 7:18 am

    #1507122

    Really good one. Fell right into the trap. 😀

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

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