Commit sub transaction and still rollback the overall transaction except for the sub transaction RRS feed

  • Question

  • I have transactions where sproc A will modify data, then call sproc B to modify other data.  all of my insert/update/delete/ sprocs have error handling where all error information including parameters are passed into an "Errlog" sproc which writes to an sql error log table.  In writing to the error log, I'm actually performing yet another nested transaction, so things might look like this:

    sproc A

      Begin Trans

        -- do something

        exec sproc B

          Begin Trans --(in sproc B)

          Begin Try

            -- do something

         Commit Trans

         End Try

         Begin Catch

          -- exec sproc to write to error log

         End Catch  -- end of sproc B

    -- Sproc B returns a prameter '@Success'  to Sproc A which tells sprocA if B failed.  If B did fail, then A rolls back the overall Transaction.

    Rolling back the transaction in sproc A will also rollback writing to the error log from B.  is there a way to commit the inner transaction where i wrote to the error log, then rollback everything else from the top most transaction?



    Tuesday, July 10, 2012 5:35 PM


All replies

  • Use table variable to write to the error log and at the end of the code insert from the table variable into your log table. Table variables don't participate in transactions.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    • Edited by Naomi N Tuesday, July 10, 2012 6:03 PM
    • Proposed as answer by HunchbackMVP Tuesday, July 10, 2012 6:07 PM
    • Marked as answer by Kalman Toth Saturday, July 14, 2012 12:52 PM
    Tuesday, July 10, 2012 6:03 PM
  • Feel free to vote on this connect item, if you think it is important having this feature in SQL Server.

    Add support for autonomous transactions


    Some guidelines for posting questions...

    • Proposed as answer by Naomi N Tuesday, July 10, 2012 6:33 PM
    • Marked as answer by Kalman Toth Saturday, July 14, 2012 12:52 PM
    Tuesday, July 10, 2012 6:08 PM
  • Thanks Naomi, the problem is that I would need to write to the table variable inside of a nested stored procedure, and then pass it as an OUT parameter back to the top level stored procedure's error handling code.  But, as we all know, we can't pass table variables as out parameters which would be an awsome feature to have.  I need an elegent way to pass the err log data back up to the top level sproc.  Additionaly, the err log data is not just one row of data.  The first row is the general error data such as the sproc name, error number and description, etc.  Then I pass a row of data for every input parameter to another errlog table which tells me the param name, data type and value.  so as you can see, i need to pass complex data back to the top level sproc (which would require the functionality of 2 table variables).


    Saturday, July 14, 2012 2:54 PM
  • You can still use a variation of Naomi's suggestion. 

    One possible way would be to make sure that everything that has been written to the error log by the current transaction can be identified.  If you can't do that with the current data you are storing in your error log, you could add a new column (of type bigint) and whenever writing to the error log, store the transaction id of the current transaction in that column.  Then, if any procedure needs to do a ROLLBACK, the procedure would first insert into a table variable all the rows (if any) in the error log for the current transaction.  Then do the ROLLBACK.  Then use the table variable to rewrite those rows into the table variable.


    Saturday, July 14, 2012 3:19 PM
  • Thanks Tom, and yes i can do that.  I write to tbSQLErrlog and tbSQLErrlogParam using uniquidentifier as the PKs and FK.  I can pass the PK to tbSQLErrlog as an out param all the way up to the top level sproc, then populate 2 table variables with this data before the rollback, then re-write the data back to the errlog tables.  I was hoping there would be a more ellegant solution out there, but at least you've confirmed that i'm not completely crazy (or i would like to think so).


    Saturday, July 14, 2012 3:30 PM
  • I did not see this thread until now.

    We have this errorlog in the system I work. Our solution is a CLR stored procedure that performs a loopback connection to write to the error log. This is only solution that is manageable in my opinion. The other solution will get out of hand as your system grows.

    Note here that a loopback connection is not the context connection, but a new connection. This requires that the assembly has the permission EXTERNAL_ACCESS.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 14, 2012 8:50 PM