We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH and TRANSACTION blocks:


        [[INSERT/update statements]] ...

    IF @@TRANCOUNT > 0
        PRINT 'Successful.'


        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
        PRINT 'Unsuccessful.'

Does it ensure the data manipulations will be rolled back in case of error(s)? Or other considerations should be taken into account?

Would be any better way of doing that (using configurations, etc)?

Thank you.

| improve this question | | | | |

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
            save transaction usp_my_procedure_name;

        -- Do the actual work here

        if @trancount = 0   
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
        if @xstate = 1 and @trancount = 0
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie. your catch should re-raise).

| improve this answer | | | | |
  • Thanks for your helpful answer and fantastic website! However I'm wondering if still I can use this pattern with a simple DML statement (not a stored proc)? Also do we have to save the transaction as below? (I don't have a store proc to use): save transaction usp_my_procedure_name; – Sky Jan 2 '13 at 22:17

What you have looks good to me. I would suggest doing something with the information of course after you have rolled back the transaction e.g. write it out to a log.

| improve this answer | | | | |
  • 1
    Thanks for your reply, could you please give me a hint how to write it to a log? – Sky Jan 2 '13 at 4:27
  • 3
    If you want to write errors or data to a log table, then before you do the rollback, copy the data you want to a table variable (It's important that you use a table variable, a temp table will be rolled back.) then perform the rollback, then insert the data from the table variable into the logging table. – HLGEM Jan 2 '13 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.