Update statements - capture errors and continue with the rest

Question

I have around 2000 SQL update commands to run and I know that some of them may fail for various reasons. I'd like to run them all, or in batches, and capture the failures for those that fail, while continuing to go through the rest of the list.

Two approaches that i'm looking at are:

XACT Abort

set xact_abort on
begin transaction
 -- Updates here --
commit transaction

Try catch

BEGIN TRY
    -- Updates here --
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
END CATCH

The problem with these is that when they fail, they fail (at least, this is what happened in my testing). Then I have to fix the problem with whichever line it fails on and start it again. I want to ignore and log any failures then continue. Is this possible using either xact_abort or a try catch type query or should I be looking at something else?

You can wrap every single update statement in a try...catch block. This is something I would probably do in a programming language using a loop and not manually in sql server. — Zohar Peled, Aug 10 '17 at 10:38
What about a logging/audit table that captures the errors? I would like to try this but I'm unsure about how to do it. — Syntax Error, Aug 10 '17 at 10:39

score 1 · Accepted Answer · answered Aug 10 '17 at 12:31

Here is something similar to what I use based on: Error and Transaction Handling in SQL Server Part One (of Three) – Jumpstart Error Handling - Erland Sommarskog

Table and procedure setup:

create table dbo.error_handler_log (
    id int identity(1,1) not null primary key
  , error_date datetimeoffset(7) not null
  , severity tinyint not null
  , [state] tinyint not null
  , [number] int not null
  , line int not null
  , [procedure] sysname null
  , message nvarchar(2048) 
);
go
create procedure [dbo].[error_handler_sp] as 
begin 
  set nocount, xact_abort on;
  declare
      @error_date datetimeoffset(7) = sysdatetimeoffset()
    , @severity tinyint = isnull(error_severity(),16)
    , @state tinyint = isnull(error_state(),1)
    , @number int = isnull(error_number(),0)
    , @line int = isnull(error_line(),0)
    , @procedure sysname = error_procedure()
    , @message nvarchar(2048) = error_message();
  insert into [dbo].[error_handler_log] 
    ([error_date],[procedure],[severity],[state],[number],[line],[message]) values 
    (@error_date, @procedure, @severity, @state, @number, @line, @message);
  --raiserror(@message, @severity, @state); /* don't re-raise error to continue code execution */
end;
go

rextester demo: http://rextester.com/EYLAFM93158

dbfiddle.uk demo

begin try;
  select 1/0 as err;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;
begin try;
  select 1/0 as err;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;
begin try;
  select 1/1 as one;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;

select *
from dbo.error_handler_log;

returns:

+-----+
| one |
+-----+
|   1 |
+-----+

+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+
| id |         error_date         | severity | state | number | line | procedure |              message              |
+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+
|  1 | 10/08/2017 13:26:00 +01:00 |       16 |     1 |   8134 |    2 | null      | Divide by zero error encountered. |
|  2 | 10/08/2017 13:26:00 +01:00 |       16 |     1 |   8134 |    8 | null      | Divide by zero error encountered. |
+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+

I've tested this and it seems to do everything I need, except, it doesn't record the actual query into the error log. Do you think that's possible? I'm not sure how to get the query into the error_handler sp. — Syntax Error, Aug 10 '17 at 15:42
@JayF1 You are correct, it doesn't record the query. It does record the `line` number of the code throwing the error though. If you really wanted to capture the sql code, then I'd say you could to put it in a variable and pass that to the procedure to insert into a column in the table. Alternatively, you could pass a parameter with a name or something that signifies a certain section of code and log that to the table. — SqlZim, Aug 10 '17 at 16:18

score 0 · Answer 2 · answered Aug 10 '17 at 11:58

May be something like this?

DECLARE @table TABLE (id int);
DECLARE @fails TABLE (updateNumber int);

INSERT INTO @table(id)
VALUES (1), (2), (3);

UPDATE @table
SET id = id + 100
WHERE id = 1;

IF @@ROWCOUNT = 0 
    INSERT INTO @fails (updateNumber) VALUES (1);

UPDATE @table
SET id = id + 100/0 -- !!!FAIL!!!
WHERE id = 2;

IF @@ROWCOUNT = 0 
    INSERT INTO @fails (updateNumber) VALUES (2);

UPDATE @table
SET id = id + 100
WHERE id = 3;

IF @@ROWCOUNT = 0 
    INSERT INTO @fails (updateNumber) VALUES (3);

SELECT * FROM @table;
SELECT * FROM @fails;

Output:

id
-----------
101
2
103

+

updateNumber
------------
2

You only need to decide what use as updateNumber to identify your failed updates.

score 0 · Answer 3 · answered Aug 10 '17 at 13:23

Take a peek at this question (asked by yours truly): SQL Server XACT_ABORT with exclusion

While the agenda in that question was to roll back everything in the event of a single failure, you can utilize the concepts there, and apply the logic to your needs.

The 30-second explanation of what I did there is: use try/catch blocks for each test, and have a section called "failure" at the end of the procedure to which all catches will be directed. You can set a message in each catch for your logging, or pass the error along to an end user.

For your needs: You would utilize a similar logic, with the omission of the XACT_ABORT as that will roll everything back in the even that one piece fails (which is the opposite of what you want). You would still want to keep the try/catch blocks and perhaps shift the logging into each CATCH block so that you can keep on rolling.

Keeping a log table with all the results of each CATCH block will help you analyze things later.

Update statements - capture errors and continue with the rest

3 Answers3