Professional Web Applications Themes

Partial rollback of transaction - Microsoft SQL / MS SQL Server

What I mean by "partial" - I need some changes to remain in database after a rollback. It shall be used in a logging system - even for a unsuccessful long transaction I need to have some info about its progress and the reason of failure to stay in a log-table, to track and yse it later. I understand, that it's "against the rules", but the problem is somehow typical and it must have a typical solution... Regards, Serg....

  1. #1

    Default Partial rollback of transaction

    What I mean by "partial" - I need some changes to remain in database after
    a rollback. It shall be used in a logging system - even for a unsuccessful
    long transaction I need to have some info about its progress and the reason
    of failure to stay in a log-table, to track and yse it later. I
    understand, that it's "against the rules", but the problem is somehow
    typical and it must have a typical solution...

    Regards, Serg.


    Serg Guest

  2. #2

    Default Re: Partial rollback of transaction

    Hello Serg !

    Just write your code and test at the end, if an Error occured ot not
    Error. If so write something in the log , if not commit the transaction.

    HTH, Jens Süßmeyer.


    Jens Guest

  3. #3

    Default Partial rollback of transaction

    Serg:

    Within the transaction you can call xp_cmdshell, and
    execute osql with an insert statement into your log table.
    The osql command does not participate in the current
    transaction, so if you rollback, the log insert still
    remains. Performance can be poor, however.

    HTH
    Vern
     
    in database after 
    for a unsuccessful 
    progress and the reason 
    it later. I 
    problem is somehow 
    Vern Guest

  4. #4

    Default Re: Partial rollback of transaction


    "Serg Prokhorov" <ru> wrote in message
    news:OUA0Pg$phx.gbl... 
    after 
    reason 

    This is a very typical problem for which there is not a very elegant
    solution.


    One option is to create a table variable to hold your log entries.
    Table variables are not transactional.
    After rolling back the transaction you can insert from the table variable
    into your log table.

    David


    David Guest

  5. #5

    Default Re: Partial rollback of transaction

    > This is a very typical problem for which there is not a very elegant 
    Thanks for the idea, but it's realy not "very elegant" - that method is goof
    for use in one stored procedure, may be two... but I want to get something
    more "universal tool" - something that I can package in a stored procedure
    call like "exec dbo.WriteLog nEventID, 'Event text' " - and I've got a new
    record in my log table, that doesn't disappear after any rollback. And that
    I'm able to use that procedure call in every place of my application...

    Serg.


    Serg Guest

Similar Threads

  1. rollback transaction erro
    By harbir in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 07:15 PM
  2. How many rollback segments can a transaction use?
    By michael ngong in forum Oracle Server
    Replies: 4
    Last Post: July 10th, 07:01 AM

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