SQL Server transaction not committing

Question

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;

I'm using MS SQL, without Go, the newly added column is not recognized. — Tibor, Nov 14 '17 at 11:31
"It runs perfectly, but it doesn't work"? I would say it doesn't run perfectly then... You need a table in the update statement. — Peter Abolins, Nov 14 '17 at 11:31
Sorry, i mistyped the update statement, the table name is there: UPDATE Table SET ColumnName = 'some value' — Tibor, Nov 14 '17 at 11:34
@PeterAbolins it does what I want, I just need to run Commit after the query. — Tibor, Nov 14 '17 at 11:40
if `@@trancount == 1` after running the script, then nothing was committed. https://learn.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql — Peter Abolins, Nov 14 '17 at 11:40
Yes, that's the question, why it doesn't commit automatically.? — Tibor, Nov 14 '17 at 11:44
Transactions apply to [DML](https://technet.microsoft.com/en-us/library/ff848766(v=sql.110).aspx), not [DDL](https://learn.microsoft.com/en-us/sql/t-sql/statements/statements). — HABO, Nov 14 '17 at 13:04

score 1 · Answer 1 · answered Nov 14 '17 at 11:30

1

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

 UPDATE Table SET ColumnName = 'some value'

answered Nov 14 '17 at 11:30

dbajtr

2,024
2
14
22

Peter Abolins · Answer 2 · 2017-11-14T12:20:53.077

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

SQL Server transaction not committing

2 Answers2