Rollback? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rollback?

Author

Topic

5fifty5
Starting Member

35 Posts

Posted - 2008-03-04 : 04:19:21

Hi.
There is a statement in Oracle, Rollback, when issued rollback's the last executed statement in Oracle through SQL Prompt (unless there was a commit or DDL before it)

Is there any statement like that for SQL Server 2000? All I could have been able to find is that unless there is a "Begin Transaction" clause you cannot use RollBack.

In other words, for instance, if i ran an update and want to rollback, what can I do to revert back the changes in SQL Server 2000?

Cheers.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 04:24:43

Yes if you have set IMPLICIT_TRANSACTIONS setting to on. In that case, execution of UPDATE/INSERT/DELETE and few other statements will start transaction automatically and you have to explicitly COMMIT or ROLLBACK the transaction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

5fifty5
Starting Member

35 Posts

Posted - 2008-03-04 : 05:24:14

so for instance in SQL Query Analyzer I write this

SET IMPLICIT_TRANSACTIONS ON

update n
set cx = '222'
from northwind

rollback

Is this what you are saying ? Correct me if I am wrong.

Secondly, this would be only for this window and I dont have to turn if off or something ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 06:12:07

Else you can explicitly start transaction & rollback/commit as:-

BEGIN TRAN Test

update n
set cx = '222'
from northwind

Use either one to commit/rollback
--COMMIT TRAN Test
--ROLLBACK TRAN Test

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 06:31:29

quote:
Originally posted by 5fifty5

so for instance in SQL Query Analyzer I write this

SET IMPLICIT_TRANSACTIONS ON

update n
set cx = '222'
from northwind

rollback

Is this what you are saying ? Correct me if I am wrong.

Secondly, this would be only for this window and I dont have to turn if off or something ?

Yes quite so and this is connection level option.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources