title: How to log errors even if the transaction is rolled back?
body: <p>Lets say we have following commands:</p>
  
  <pre><code>SET XACT_ABORT OFF;
  SET IMPLICIT_TRANSACTIONS OFF
  
  DECLARE @index int 
  SET @index = 4;
  
  DECLARE @errorCount int
  SET @errorCount = 0;
  
  BEGIN TRANSACTION
      WHILE @index &gt; 0
      BEGIN
          SAVE TRANSACTION Foo;
          BEGIN TRY
              -- commands to execute...
              INSERT INTO AppDb.dbo.Customers VALUES('Jalal', '1990-03-02');
  
              -- make a problem
              IF @index = 3
                  INSERT INTO AppDb.dbo.Customers VALUES('Jalal', '9999-99-99'); 
          END TRY
          BEGIN CATCH
              ROLLBACK TRANSACTION Foo; -- I want to keep track of previous logs but not works! :(
              INSERT INTO AppDb.dbo.LogScripts VALUES(NULL, 'error', 'Customers', suser_name());
              SET @errorCount = @errorCount + 1;
          END CATCH
          SET @index = @index - 1;
      END
  IF @errorCount &gt; 0
      ROLLBACK TRANSACTION
  ELSE
      COMMIT TRANSACTION
  </code></pre>
  
  <p>I want to execute a batch, keep all errors in log and then, if no error was occurred, commit all changes. How can implement it in Sql Server?</p>