CODEWITHSUNDEEP
sql-servert-sqltransactionscommit
Tibor
Tibor

Reputation: 29

SQL Server transaction not committing

I have a SQL Server transaction which adds a column to a table, and then it adds some value to that column. It runs perfectly, but it doesn't commit the changes.

I checked with @@trancount, and it's value is 1 after running the query.

What is wrong with the transaction?

Thanks!

BEGIN TRANSACTION
    ALTER TABLE Table
        ADD ColumnName VARCHAR(200) NULL;
    GO

    BEGIN TRY
        UPDATE ColumnName 
        SET ColumnName = 'some value'

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH;

Upvotes: 2

Views: 6039

Answers (2)

Peter Abolins
Peter Abolins

Reputation: 1539

If you do it this way, it will work. XACT_ABORT will automatically rollback the transaction if an error occurs at runtime. I have had problems with using TRY...CATCH in the past, and in this case, it doesn't seem warranted.

SET XACT_ABORT ON
BEGIN TRANSACTION
    ALTER TABLE Table
        ADD ColumnName VARCHAR(200) NULL;

    UPDATE Table 
        SET ColumnName = 'some value';

    COMMIT TRANSACTION;

SET XACT_ABORT OFF

UPDATE

I found this, which explains the process better, if you still want to use the TRY...CATCH construct.

Try...Catch inside Transactions

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

You need to change your update to tell it which table to update.

 UPDATE Table SET ColumnName = 'some value'

Upvotes: 1

Related Questions