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:
BEGIN TRY
BEGIN TRANSACTION
[[INSERT/update statements]] ...
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
PRINT 'Successful.'
END
END TRY
BEGIN CATCH
SELECT
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
BEGIN
ROLLBACK TRANSACTION
PRINT 'Unsuccessful.'
END
END CATCH
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.
Best Answer
I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:
This pattern checks the
XACT_STATE()
in the catch block to guard against uncommittable transactions: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).