1

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?

Thank you.

  • 1
    You may be able to use a table variable depending on what you want to do. Table varibles do not participate in tranactions and are not affected by rollbacks. – Brian Bentley Jun 27 '14 at 13:49
  • Yes, that's the way we are going now - log entries are duplicated in a local table variable and if we have to roll back, the log table is re-populated from the variable. It sort of works, but I am looking at improving this anyway. Just in case some really bad stuff happens, the transaction goes away and the catch section never occurs and then the log is lost. – Yuri Makassiouk Jun 27 '14 at 13:58
1

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.

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.