Home SQL Server transaction not committing

# 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.
Processed in 0.316383 second(s) , Gzip On .