Problem with Alter Then Update in Try Catch with Tran Using Transact-Sql

Problem with alter then update in try catch with tran using Transact-SQL

Even though I am writing my own answer - all credit goes to @Mikael Eriksson, who suggested that I need to separate different batches with a GO - so that the code that alters the table does not conflict with code that uses the altered table. Thanks Mikael!

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

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;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

    -- transform logic.
   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'ABC',
               ACCOUNTNUMBER = ABC.ACCOUNTNUMBER,
               PUK = ABC.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  ABC
   ON          RS.SERVICE_NUMBER = ABC.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'DEF',
               ACCOUNTNUMBER = DEF.BILLINGACCOUNTNUMBER ,
               PUK = DEF.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  DEF
   ON          RS.SERVICE_NUMBER = DEF.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'No Match'
   WHERE       SOURCE IS NULL;

    -- Fix other columns that should be not nullable.
   alter table dbo.RETRIEVAL_STAT
      alter column SERVICE_NUMBER nvarchar (50) NOT NULL;
   alter table dbo.DEF
      alter column PUK nvarchar (20) NOT NULL;

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;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

T-SQL: ALTER VIEW error within a transaction and try-catch block

This didn't give me any syntax error, try this. I just removed "Go" at the end of dynamic sql.

USE [MyDatabase]
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
BEGIN TRANSACTION
go
BEGIN TRY
EXEC sp_executesql N'ALTER VIEW dbo.MyView
AS
SELECT * FROM ....; '
EXEC sp_executesql N'ALTER TABLE ...;'
EXEC sp_executesql N'UPDATE ....;'
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;

IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

SQL Server try catch not working on create, alter table and adding PK constraint

There is a solution for this:

https://dba.stackexchange.com/questions/29544/rollback-group-of-ddl-statements

and more about it:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

which lead me to this solution:

SET XACT_ABORT ON; 

BEGIN TRAN
BEGIN TRY
    CREATE TABLE dbo."aCTIONS" ("ID" int  NOT NULL IDENTITY(1,1) )

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "Date" datetime

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "Name" nvarchar(50) COLLATE Latin1_General_CI_AS

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "ActionID" int;

    ALTER TABLE "dbo"."aCTIONS" 
        ADD CONSTRAINT PK_aCTIONS_ActionID 
            PRIMARY KEY NONCLUSTERED ("ActionID") 

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "Notes" nvarchar(255) COLLATE Latin1_General_CI_AS;

    ALTER TABLE "dbo"."aCTIONS" 
        ADD CONSTRAINT DF_aCTIONS_Notes DEFAULT (N'MISLIM') FOR "Notes"

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "Consequence_Paid" bit;

    ALTER TABLE "dbo"."aCTIONS" 
        ADD CONSTRAINT DF_aCTIONS_Consequence_Paid DEFAULT ((1)) FOR "Consequence_Paid"

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "Reward_Paid" bit

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "username" int

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "time" time(0)

    ALTER TABLE "dbo"."aCTIONS" 
        ADD "DateOnly" date;

    ALTER TABLE "dbo"."aCTIONS" 
        ADD CONSTRAINT DF_aCTIONS_DateOnly DEFAULT (getdate()) FOR "DateOnly"
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1  
    BEGIN
        ROLLBACK TRAN
        THROW
    END
END CATCH
IF (XACT_STATE()) = 1  
    COMMIT TRAN

and the table wasn't created because of an error. Pretty cool, isn't it?

Add column to table and then update it inside transaction

GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cut the file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.

Also, you cannot catch exceptions without checking for the XACT_STATE() to ensure the transaction is not doomed.

Using GUIDs for IDs is always at least suspicious.

Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}' also cannot be correct.

Updated:

Separate the ALTER and UPDATE into two batches.
Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
use XACT_ABORT to force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.

example script:

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

Only a successful script will reach the COMMIT. Any error will abort the script and rollback.

I used COLUMNPROPERTY to check for column existance, you could use any method you like instead (eg. lookup sys.columns).

Using try/catch for update

A try/catch block like this...

BEGIN TRY
    -- Your Code Goes 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

...is going to help you locate the problem in your SQL code. If this was in a stored procedure you could also return the parameters (i.e. add a SELECT @RecordID AS [RecordID] to that list in the catch block). Moving forward though, if you are running into problems with the actual data I would encourage you to look at adding foreign keys and other constraints to protect the logical integrity of your database. Ideally at a minimum you cannot put data into the database which will break your stored procedures.

EDIT

Refering to you're most recent edits, if you put the UPDATE inside a stored procedure and catch the error, then replace your update series with calls to that procedure the remaining updates would continue, and you could return/track/log the error within the SP's catch block however you wished to.

writing a transaction in t-sql and error handling

If you're using SQL 2005 or later, you can use the TRY...CATCH block, like this:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Contact.Receiver(First_Name, Last_Name, Email) VALUES (@First_Name, @Last_Name, @Email); 
    ... other inserts etc 
    ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

This way, you don't keep repeating the same blocks of code checking @@ERROR. If you want to know what error occurred, in the BEGIN CATCH block you can get various bits of info:

ERROR_NUMBER() returns the number of the error.

ERROR_SEVERITY() returns the severity.

ERROR_STATE() returns the error state number.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger
where the error occurred.

ERROR_LINE() returns the line number inside the routine that caused the
error.

ERROR_MESSAGE() returns the complete text of the error message. The text
includes the values supplied for any
substitutable parameters, such as
lengths, object names, or times.

SQL Transaction uncommittable while using try..catch.. Why?

The reason is: SQL Server dooms the transaction WHENEVER an error occurs, whatever the error is, whether it is in a TRY block or not, whether you saved a transaction state or not, whether the error occurs in a procedure or not, whatever you do.

When the error occurs in one of the procedure calls, the transaction is doomed. You can only rollback it completely (any savepoint will not help).

At the end, since the transaction is doomed, you cannot commit it...

Try this:

SET XACT_ABORT OFF -- pityful attempt to avoid the doom
BEGIN TRANSACTION
--
-- some useful TSQL instructions could be here
--
SAVE TRANSACTION SQL_SERVER_IS_GARBAGE -- another pityful attempt to do a partial restore
PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
BEGIN TRY
  DECLARE @n int
  SELECT @n = CONVERT(int,'ABC') -- some very benign data error here (example)
  COMMIT TRANSACTION -- will never reach here
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
  PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
  IF XACT_STATE()=-1 BEGIN
    PRINT 'The transaction is doomed, say thanks to Sql Server!'
    PRINT 'CANNOT restore to the save point!'
    -- You can just cry here and abort all, you lost all the useful work
    ROLLBACK TRANSACTION
  END
  ELSE BEGIN
    -- would restore before the error if the transaction was not doomed
    ROLLBACK TRANSACTION SQL_SERVER_IS_GARBAGE -- will never reach here either!
  END  
END CATCH

How to use SQL Server Transaction inside T-SQL TRY...CATCH block

TRY/CATCH blocks can only be used inside the scope a single request (batch). You are trying to span a TRY block over several batches. GO is the default batch delimiter, fyi.

You also expect to support transactions and rollback for some operations that do not support transactions, like RESTORE:

RESTORE is not allowed in an explicit or implicit transaction.

You would have a much better chance at handling this kind of problems at a higher level, in an app or even is a script. Note that sqlcmd supports the -b option to break on first error, and you can use the exit code (%ERRORLEVEL%).

Problem with Alter Then Update in Try Catch with Tran Using Transact-Sql