Question 103388 in Azure Question posted in
The official documentation to get you started can be found here.

Azure SQL Server: Execute SQL command only after the previous one has been executed

I have a database running in Microsoft Azure.
I want to run a script in it. The problem is that the script only works if the SQL commands are run one at a time. So several commands are executed at the same time. For example, SQL wants to update a column before the column is even created.

E.g. I want to make sure that the SQL command ADD is executed first before UPDATE is executed.

How can I make it so that all commands in the script are executed one after the other and not in parallel?

As an error I get

Failed to execute query. Error: Invalid column name 'table_1_id'

Here is my code:

BEGIN TRY
    BEGIN TRANSACTION
        ALTER TABLE table_1
        ADD table_1_id BIGINT;
        UPDATE table_1 SET table_1_id = table_1_id2;

        ALTER TABLE table2
        DROP CONSTRAINT constraint1;

        ALTER TABLE table_1 
        DROP CONSTRAINT PK_1;

        ALTER TABLE table_1 
        DROP COLUMN table_1_id2;

        ALTER TABLE table_1 
        ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH
Tags:

2

Answers


  1. 0

    Putting it in answer instead:

    BEGIN TRY
    BEGIN TRANSACTION
        ALTER TABLE table_1
        ADD table_1_id BIGINT;
        EXEC(N'UPDATE table_1 SET table_1_id = table_1_id2;')

        ALTER TABLE table2
        DROP CONSTRAINT constraint1;

        ALTER TABLE table_1 
        DROP CONSTRAINT PK_1;

        ALTER TABLE table_1 
        DROP COLUMN table_1_id2;

        ALTER TABLE table_1 
        ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

    This allows the new column to be visible to the update.
    You can use the same technique for other parts if they’re crashing

    Testscript:

    create table table_1 (table_1_id2 bigint)
GO

ALTER TABLE table_1
    ADD table_1_id BIGINT;
  exec('UPDATE table_1 SET table_1_id = table_1_id2;')

  go

  drop table table_1
    Login or Signup to reply.
  2. 0

    Here in below code I have changed update statement to run in dynamic sql and it is working fine.

    Code

    BEGIN TRY
BEGIN TRANSACTION   
    ALTER TABLE table_1 
    ADD table_1_id BIGINT;
    
    --changed to dynamic sql
    DECLARE @sql NVARCHAR(2048) = 'UPDATE table_1 SET table_1_id = table_1_id2;';
    EXEC sys.sp_executesql @query = @sql;

    ALTER TABLE table2
    DROP CONSTRAINT constraint1;

    ALTER TABLE table2 
    DROP CONSTRAINT PK_1;

    ALTER TABLE table2 
    DROP COLUMN table_1_id2;

    ALTER TABLE table2 
    ADD table_1_id2 BIGINT IDENTITY PRIMARY KEY;    
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000);  
DECLARE @ErrorSeverity INT;  
DECLARE @ErrorState INT;  

SELECT   
   @ErrorMessage = ERROR_MESSAGE(),  
   @ErrorSeverity = ERROR_SEVERITY(),  
   @ErrorState = ERROR_STATE();  

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
END CATCH

    Result

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search