CODEWITHSUNDEEP
sqlsql-server
Syntax Error
Syntax Error

Reputation: 1640

Update statements - capture errors and continue with the rest

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?

Upvotes: 4

Views: 6450

Answers (3)

Eli
Eli

Reputation: 2608

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.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

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. |
+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+

Upvotes: 1

Ruslan K.
Ruslan K.

Reputation: 1981

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.

Upvotes: 0

Related Questions