How can I make sure all SQL Updates are Executed

Question

We ran into an issue in our production environment where only the first 2 updates ran and the last 2 didn't. What can we implement when the following script is run from BIDS (sql server) to ensure each of the 4 sql update statements is run? Do we need to wrap the whole thing in Try...….Catch?

Below is basically now my final working code used in production.

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION

-- Batch 0
BEGIN TRY

    --There is a step prior to this step to delete table
    Insert Into Table X (Field1, Field2, Field3)

    Select
            Field1
            ,Field2
            ,Field3
    FROM Table Loans

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  ROLLBACK TRANSACTION;
END CATCH;
GO

-- Batch 1
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update A Fields
    Update X Table
    Set X Field4 = A Field
    From X Table
    Left OUTER JOIN A Table
        ON X Key = A Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 2
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update B Fields
    Update X Table
    Set X Field5 = B Field
    From X Table
    Left OUTER JOIN B Table
        ON X Key = B Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 3
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update C Fields
    Update X Table
    Set X Field5 = C Field
    From X Table
    Left OUTER JOIN C Table
        ON X Key = C Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 4
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update D Fields
    Update X Table
    Set X Field7 = D Field
    From X Table
    Left OUTER JOIN D Table
        ON X Key = D Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Commit transaction
IF XACT_STATE() = 1
BEGIN
  COMMIT TRANSACTION;
  PRINT 'Transaction committed.';
END;

Without knowing how it is failing it is not possible to suggest anything. Have you checked the logs? As you suspect, catching the error would certainly help but ensure that you print/write any errors to the log. Also, can you recreate the error with test data. — Peter Smith, Dec 05 '18 at 19:02
Unfortunately we don't have permission to view master tables. Is there another way query the logs? I could open a case and have the host retrieve the logs for us. I haven't been able to recreate, this was the first time this has happened. We are in a hosted environment, is it possible there was a connection issue? In Bids the execution sql task completed with the green check box but the last 2 updates didn't take effect. — chris, Dec 05 '18 at 19:26
It could be many things. If you can't access the logs then adding diagnostic output might help, — Peter Smith, Dec 05 '18 at 20:28

Ben · Accepted Answer · 2018-12-05T19:14:30.063

2

I would wrap it in a transaction and it would auto rollback the changes if something went wrong.

BEGIN TRANSACTION
    your tsql code...
COMMIT

While still using the transaction, wrap your code in a try/catch and then you can select/print/log your error. Try...Catch (Transact-SQL)

edited Dec 05 '18 at 19:14

answered Dec 05 '18 at 18:56

Ben

1,820
2
14
25

I've updated my question with an example from your link and test in production and worked but will it actually catch issues for each update? – chris Dec 06 '18 at 14:10
If there is an error anywhere in the TRY then it will stop where it is and go directly to the catch. I'm assuming that is what you mean when you say "catch issues for each update". Try manually throwing an error to test that it works the way you are intending -> https://stackoverflow.com/questions/1531450/raise-an-error-manually-in-t-sql-to-jump-to-begin-catch-block. Also please review the section, "Errors Unaffected by a TRY...CATCH Construct" from the link in my answer. – Ben Dec 06 '18 at 21:09
Did this answer your question? If so, please mark as correct answer. – Ben Dec 10 '18 at 13:14
Yes this is working the way it should. I don't see how to mark as correct answer. – chris Dec 10 '18 at 17:05
https://stackoverflow.com/help/someone-answers - click on the check mark beside the answer to toggle it from greyed out to filled in – Ben Dec 10 '18 at 17:07

score 1 · Answer 2 · answered Dec 05 '18 at 21:06

It all depends how your production environment treats server errors.

If, say, the 3rd update fails due to a foreign key violation, the server returns an error to the client. The client deals with that however it deals with it. Sort of an abort, retry, or ignore situation. Depending on the actual problem, the batch is not necessarily terminated, so unless the client acts the 4th update might be executed anyway.

The server also returns the exit status of every stored procedure to the client, which the client is similarly free to honor or ignore. You might also wrap the whole thing in a stored procedure. After each UPDATE, test @@error, and return if it's set.

If your production environment is completely unknown or unreliable, you can call your own stored procedure and write its name and returned status to another table, perhaps along with the execution time. Then you can query that table to see if it succeeded or not.

How can I make sure all SQL Updates are Executed

2 Answers2