How to turn off the auto commit mode?

[sonnydo]

Hi everyone,

I'd like to know how to turn the auto commit mode off when I issue the
update statement. In other word, I want to be able to roll back after I
issue the update command.

Thanks,
Sonny Do

[Juan Carlos Avila]

Hi Sonny,

Option 1:

Before each data modification command, issue a BEGIN TRAN command.
This lets you issue an explicit COMMIT TRAN / ROLLABCK after the
data modification command. For example,

BEGIN TRAN -- Beginning of transaction
go
UPDATE ...
go
:
:
ROLLBACK -- End of transaction
go

Option 2:

You can try using the "SET CHAINED ON" T-SQL command. While in
chained mode, the server implicitly executes a BEGIN
TRAN command before the first data modification command. In
order to commit/rollback the transaction you will have to
issue an explicit COMMIT / ROLLBACK command.

The SET command works at the session level, meaning that you
will have to execute the SET CHAINED ON command each time you
log in to the server (OFF is the default). For example,

SET CHAINED ON
go

UPDATE ... -- Beginning of transaction
go
:
:
COMMIT TRAN / ROLLBACK -- End of transaction
go

Please see your reference manuals for more information.

Hope this helps.

Juan C. Avila
MT-Base / Sybase de Colombia
jav...@mtbase.com

T: (57-1) 629 1480
F: (57-1) 629 1425
Internet: www.mtbase.com

sonnydo wrote in message ...