xact_abort is off, why does one non-fatal statement cause the whole sproc to abort?

  • This is killing me. Please help.

    "select @@options & 13684" yields 0 which tells me xact_abort is off. Why then, when I execute:

    exec spfoo (40) -- definition at bottom

    does the procedure throw a fatal error at the first insert and not complete the rest of the code? I thought that by default, constraint violations are not fatal, and so, subsequent code would be executed?

    create procedure spfoo (@a int)

    as

    SET XACT_ABORT OFF

    SET IMPLICIT_TRANSACTIONS OFF

    declare @localerr int

    set @localerr = 0

    insert into foo (a, b)

    values (@a, 'spfoos')

    set @localerr = @@ERROR

    insert into foo (a, b)

    values (1, 'you should see this!')

    return 0

    go

  • Can you provide the table definition for "foo"?  I want to ensure that I am exactly duplicating the test case.

    Thanks

    Wayne

  • Happily Wayne. Thanks for the response. Some of the spfoo code may have changed as I've been working on it, but the error will still be apparent.

    create table fkoo ( -- the FK table used to cause an error

    afk int not null Primary key)

    go

    create table foo ( -- the table being inserted into

    a int foreign key references fkoo(afk),

    b varchar(128))

    go

    insert into fkoo (afk) -- valid FK values

    values (1)

    go

    insert into fkoo (afk)

    values (2)

    go

    insert into fkoo (afk)

    values (3)

    go

    create procedure spfoo (@a int)

    -- calling this sproc with anything other than 1, 2, or 3 will cause an FK

    -- constraint violation

    -- might be different than the original one listed. I was trying some stuff.

    as

    BEGIN TRANSACTION XXX

    declare @localerr int

    set @localerr = 0

    insert into foo (a, b)

    values (@a, 'spfoos')

    set @localerr = @@ERROR

    insert into foo (a, b)

    values (1, 'this code should be executed!')

    select 'post error code has been executed'

    if @@ERROR = 0

    begin

    commit transaction XXX

    return 0

    end

    else

    begin

    rollback transaction XXX

    return 1

    end

    go

  • Thanks for the extra info.  I'll play with it in just a bit.

    Have you tried running exactly the example from books on line?  From the topic "SET XACT_ABORT"?  What are the results?

    Wayne

     

  • I just ran the BOL example, and it worked like expected (rows 1 and 3 added). I made it a point to run it in our production database. Could have it have anything to do with naming the transactions? The fact that my code is in a stored procedure?

    Thanks again for the help Wayne.

  • Well, we have demonstrated that the database behaves as expected when presented with correct code.  Since the database is not changing between test runs, that suggests that the problem lies within your code.

    I see one logical error fairly quickly in the following code:

    select 'post error code has been executed'

    if @@ERROR = 0

    begin

    commit transaction XXX

    return 0

    end

    else

    begin

    rollback transaction XXX

    return 1

    end

    You are testing @@Error instead of @localerr.  @@Error will always equal zero in this case as the select is unlikely to fail.  The effect here is that you will always commit your transaction unless there is a catastrophic failure before getting to that line.

    Also, from books on line: "It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server."  Because you are working in an explicit transaction, I suspect that SQL Server is helping you out by turning XACT_ABORT back on without telling you. 

    hth

    Wayne

  • you're right on testing @@error. I should be testing @localerr. point taken.

    The issue however, is the fact that NO CODE after the failing statement in spfoo gets executed, including the line immediately after.

    If you debug this sproc in Query Analyzer and give @a an invalid value (> 3), you'll see that even the debugger kicks out on that insert line.

    This is the crux of the biscuit. I didn't see that bit in BOL about xact_abort on explicit transactions. How do I get there? Why would that be the case? What's the point of having explicit transactions if the batch will always fail on error?

    thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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