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

Question

Here is some Transact-SQL I am trying to run via sqlcmd (SQL Server 2005).

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;

   -- transform logic.
   update dbo.RETRIEVAL_STAT set
      SOURCE = 'XX',
      ACCOUNTNUMBER = 'XX',
      PUK = 'XX';

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

I am getting the following error:

(0 rows affected)
Changed database context to 'PUK'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'SOURCE'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'ACCOUNTNUMBER'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'PUK'.

I am guessing that this is because the new columns introduced by the alter statement have not yet been committed, so that the update fails.

My question is how do I get this to work then? I want this to run as a single transaction that I can rollback if something goes wrong.. This is important because I have more alter statements to include yet, and am a bit frustrated that I can't get past this point.

Any assistance would be most appreciated!

Rob :)

score 5 · Accepted Answer · answered Sep 17 '11 at 03:34

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

I wrote this up here: [Separate DDL and DML in Transact-SQL with Batches](http://robertmarkbramprogrammer.blogspot.com/2011/09/separate-ddl-and-dml-in-transact-sql.html). — Robert Mark Bram, Sep 17 '11 at 07:16

Mikael Eriksson · Answer 2 · 2011-09-15T06:02:24.057

4

SQL Server checks the columns when the code is compiled. You could use dynamic sql to get around that problem.

-- transform logic.
declare @SQL nvarchar(1000)
set @SQL = 
  'update dbo.RETRIEVAL_STAT set
     SOURCE = @S,
     ACCOUNTNUMBER = @A,
     PUK = @P';

declare @Params nvarchar(1000);
set @Params = N'@S nvarchar(10), @A nvarchar(10), @P nvarchar(20)';

exec sp_executesql @SQL, @Params, N'S', N'A', N'P';

edited Sep 15 '11 at 06:02

answered Sep 15 '11 at 05:49

Mikael Eriksson

136,425
22
210
281

Well, thank you for the suggestion - I think that this means I need to break up my script though - because I have a lot of SQL that references those new columns (sql which doesn't need to be made dynamic) – Robert Mark Bram Sep 16 '11 at 02:42
@Robert Mark Bram - I don't see why you think you need that. Your problem with this code is mixing DML and DDL in the same batch. `GO` is the batch separator and every batch i compiled on it's own. The other code that does not "need" to use dynamic because it is part of another batch. A better solution for your problem is to move the code where you add the columns to a batch of it's own, right before the batch where you add the data. The difference in outcome would be that if the update fails you will still have the columns in your table. – Mikael Eriksson Sep 16 '11 at 05:14
Ohhhhh, so that's what you use GO for! After a while, I was blindly inserting GOs everywhere... now I know how to use them properly I hope. :) – Robert Mark Bram Sep 17 '11 at 03:31
One more question - if I may.. how do I prevent the second batch from executing if the first failed? Thanks again Mikael - you have helped me a lot. – Robert Mark Bram Sep 17 '11 at 03:41
@Robert - Glad to hear that I have helped. I think you should ask that question as a new question so more users than I have a chance to give better answer than I can give. – Mikael Eriksson Sep 17 '11 at 05:17
@RobertMarkBram Just came across this and was wondering if you asked the new question? If so please link (...think the answer may be as simple as `SET XACT_ABORT ON;`?) – Steve Chambers Feb 06 '17 at 14:25
1

This is it @SteveChambers: http://stackoverflow.com/questions/7453892/transact-sql-how-to-prevent-second-batch-from-running-if-the-first-failed – Robert Mark Bram Feb 06 '17 at 19:37

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

2 Answers2

Linked