Home SQL Server transaction not committing
Reply: 2

SQL Server transaction not committing

Tibor
1#
Tibor Published in 2017-11-14 11:22:48Z

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;
dbajtr
2#
dbajtr Reply to 2017-11-14 11:30:43Z

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

 UPDATE Table SET ColumnName = 'some value'
Peter Abolins
3#
Peter Abolins Reply to 2017-11-14 12:20:53Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.316383 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO