I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example:
BEGIN TRY BEGIN TRAN -- Run updates 01 INSERT INTO dbo.Log (Text) VALUES ('Updates 01 - big success'); -- Run updates 02 INSERT INTO dbo.Log (Text) VALUES ('Updates 02 - big success'); -- and so on... COMMIT END TRY BEGIN CATCH ROLLBACK INSERT INTO dbo.Log (Text) VALUES ('At a certain step there was a problem: ' + ERROR_MESSAGE()); END CATCH
Of course, in case of errors we lose the success log entries done inside the transaction and are left only with the last (error) message inserted after the rollback took place. Is there a way to make those INSERT INTO dbo.Log statements "persistant" before the transaction finishes? I guess, if it were, it would fly in the face of proper transaction control, so I on some level hope this doesn't exist, but maybe somewhere in the category of tricks?