Professional Web Applications Themes

Correct placement of SET XACT_ABORT ON - Microsoft SQL / MS SQL Server

One of my developers has used the T-SQL command SET XACT_ABORT ON (which I confess I was unfamiliar with) in his stored procedures. I have reason to believe that the transaction is failing, for one reason or another, to store the data. In looking at his code he had a statement like this: BEGIN TRANSACTION SET XACT_ABORT ON However, in looking at the online help, they had the sequence of statements like this: SET XACT_ABORT ON BEGIN TRANSACTION My question is, does it matter? Rod...

  1. #1

    Default Correct placement of SET XACT_ABORT ON

    One of my developers has used the T-SQL command SET XACT_ABORT ON (which I
    confess I was unfamiliar with) in his stored procedures. I have reason to
    believe that the transaction is failing, for one reason or another, to store
    the data. In looking at his code he had a statement like this:

    BEGIN TRANSACTION
    SET XACT_ABORT ON

    However, in looking at the online help, they had the sequence of statements
    like this:

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    My question is, does it matter?

    Rod




    Rod Guest

  2. #2

    Default Re: Correct placement of SET XACT_ABORT ON

    Apparently, it does not matter (at least in v7). Try the following as is
    and notice that the batch continues to the end. Then uncomment one of the
    xact_abort lines, run , etc. Regardless of placement, the batch aborts at
    the 2nd insert.

    SET ANSI_WARNINGS ON
    go
    IF OBJECT_ID('TEST') is not null DROP TABLE TEST
    GO

    CREATE TABLE TEST ( CTEST CHAR(10))
    GO

    SET XACT_ABORT OFF
    --SET XACT_ABORT ON
    BEGIN TRANSACTION
    --SET XACT_ABORT ON
    INSERT TEST (CTEST) VALUES ( '0123456789')
    INSERT TEST (CTEST) VALUES ( '01234567890')
    SELECT 'CONTINUE'
    ROLLBACK TRAN
    GO

    IF OBJECT_ID('TEST') is not null DROP TABLE TEST
    GO


    "Rod" <SPAM> wrote in message
    news:%phx.gbl... 
    store 
    statements 


    Scott Guest

  3. #3

    Default Re: Correct placement of SET XACT_ABORT ON

    OK, then, we must be having another problem. We have 2 stored procedures
    which start a transaction, set XACT_ABORT on and then insert values into 2
    different tables, but I know for a fact that sometimes one table gets its
    values but its child table does not. I know I am not showing the code, (I
    don't have it here with me at the moment), but generally speaking what could
    be causing this problem?

    Rod

    "Scott Morris" <com> wrote in message
    news:phx.gbl... [/ref]
    I [/ref]
    to 
    > store 
    > statements 
    >
    >[/ref]


    Rod Guest

  4. #4

    Default Re: Correct placement of SET XACT_ABORT ON

    Rod (SPAM) writes: 

    The problem with SET XACT_ABORT on is that it does not affect all
    errors. It does not affect:

    o Compilation errors, for instance missing table, errors in dynamic SQL
    etc.
    o Error 266, trancount mismatch when leaving a stored procedure.
    o RAISERROR.

    Note that due to deferred name resolution, compilation errors may well
    happen at run-time. If a stored procedure refers to a non-existing table,
    the query will fail when you reach that execution path. See further
    http://www.algonet.se/~sommar/error-handling-I.html#scope-abortion.

    If this does not help you in the right direction, I suggest that you
    post the code, or a sample that demonstrates what's happening.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Div Placement
    By rayezra in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: January 23rd, 02:07 AM
  2. my.cnf placement
    By Ted in forum MySQL
    Replies: 0
    Last Post: July 18th, 08:42 PM
  3. Data placement
    By ThomasHuber in forum Macromedia ColdFusion
    Replies: 3
    Last Post: September 17th, 03:23 AM
  4. Placement
    By Tim Midgett in forum ASP Database
    Replies: 2
    Last Post: July 14th, 07:10 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