Rollback with now transaction statement

Question

Rollback with now transaction statement

ryan james

Valued Member

Points: 57
More actions
July 11, 2006 at 9:05 am

#60662

Hi All,
Is it possible to roll back a statement (i.e. UPDATE) if it is not in a transaction statement. I was previously using Oracle 9 which ya simply type "rollback" and that would rollback the last statement, im now using SQL Server 2000 and it seems that ya can only rollback using condition statements in transaction statements.
The reason i want to do this is that i am manually updating tables with update statements... well and sometimes $hit happens! And ya need to undo/rollback.
If u know how please help
Ryan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

In SQL Server, you cannot issue a rollback unless you have first issued a begin transaction command. A simple update statement outside of a begin/end transaction will still be part of an implicit transaction, but you will not be able to rollback using the rollback statement. I would suggest that you place all of your manual updates inside an explicit transaction.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ian Yates SSCoach Points: 19738 More actions · Answer 2

For more info on SQL Server and implicit transactions look up

set implicit_transactions ON/OFF in SQL Server Books Online (the "help file" for SQL Server)

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

You can try setting implicit_transactions. It is limited commitment control on the last statement run. By default it is set to off. If you set it on it will allow you to rollback the last statement run.

Tom