CODEWITHSUNDEEP
sqlsql-servertransactions
Jalal
Jalal

Reputation: 6836

How to log errors even if the transaction is rolled back?

Lets say we have following commands:

SET XACT_ABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF

DECLARE @index int 
SET @index = 4;

DECLARE @errorCount int
SET @errorCount = 0;

BEGIN TRANSACTION
    WHILE @index > 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 > 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

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?

Upvotes: 3

Views: 3527

Answers (1)

StuartLC
StuartLC

Reputation: 107247

The transaction is tied to the connection, and as such, all writes will be rolled back on the outer ROLLBACK TRANSACTION (irrespective of the nested savepoints).

What you can do is log the errors to an in-memory structure, like a Table Variable, and then, after committing / rolling back the outer transaction, you can then insert the logs collected.

I've simplified your Logs and Customers tables for the purpose of brevity:

CREATE TABLE [dbo].[Logs](
    [Description] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL
);
GO

And then you can track the logs in the table variable:

SET XACT_ABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF
GO

DECLARE @index int;
SET @index = 4;

DECLARE @errorCount int
SET @errorCount = 0;

-- In memory storage to accumulate logs, outside of the transaction
DECLARE @TempLogs AS TABLE (Description NVARCHAR(MAX));

BEGIN TRANSACTION
    WHILE @index > 0
    BEGIN
        -- SAVE TRANSACTION Foo; As per commentary below, savepoint is futile here
        BEGIN TRY
            -- commands to execute...
            INSERT INTO Customers VALUES(1, 'Jalal');

            -- make a problem
            IF @index = 3
                INSERT INTO Customers VALUES(NULL, 'Broken'); 
        END TRY
        BEGIN CATCH
            -- ROLLBACK TRANSACTION Foo; -- Would roll back to the savepoint
            INSERT INTO @TempLogs(Description) 
               VALUES ('Something bad happened on index ' + CAST(@index AS VARCHAR(50)));
            SET @errorCount = @errorCount + 1;
        END CATCH
        SET @index = @index - 1;
    END
IF @errorCount > 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION
-- Finally, do the actual insertion of logs, outside the boundaries of the transaction.
INSERT INTO dbo.Logs(Description)
    SELECT Description FROM @TempLogs;

One thing to note is that this is quite an expensive way to process data (i.e. attempt to insert all data, and then roll back a batch if there were any problems encountered). An alternative here would be to validate all the data (and return and report errors) before attempting to insert any data.

Also, in the example above, the Savepoint serves no real purpose, as even 'successful' Customer inserts will be eventually rolled back if any errors were detected for the batch.

SqlFiddle here - The loop is completed, and despite 3 customers being inserted, the ROLLBACK TRANSACTION removes all successfully inserted customers. However, the log is still written, as the Table Variable is not subjected to the outer transaction.

Upvotes: 3

Related Questions