Transaction question

Transaction question

Post by MAB7 » Thu, 13 Nov 2003 09:15:24



I wrote a procedure in SQL 6.5 which is something like

CREATE  PROCEDURE  abc

AS

BEGIN TRAN xyz

insert statement ..

insert statement ...

update statement...

insert statement ...

insert statement ...

COMMIT TRAN xyz

I want either all statements to execute or none.

My question is, if an error occurs like a constraint is violated, where will
the control pass? to the next statement or will it exit the procedure? and
do I need to explicitly issue a rollback tran? and if yes then where? and if
no then will the server itself rollback the changes made prior to the error?

thx

 
 
 

Transaction question

Post by Ron Talmag » Thu, 13 Nov 2003 09:31:51


MAB71,


value. If there is an error, then roll back the transaction and exit from
the stored procedure.

Fatal errors will automatically roll back the transaction and abort the
batch.

Ron
--
Ron Talmage
SQL Server MVP

Quote:> I wrote a procedure in SQL 6.5 which is something like

> CREATE  PROCEDURE  abc

> AS

> BEGIN TRAN xyz

> insert statement ..

> insert statement ...

> update statement...

> insert statement ...

> insert statement ...

> COMMIT TRAN xyz

> I want either all statements to execute or none.

> My question is, if an error occurs like a constraint is violated, where
will
> the control pass? to the next statement or will it exit the procedure? and
> do I need to explicitly issue a rollback tran? and if yes then where? and
if
> no then will the server itself rollback the changes made prior to the
error?

> thx


 
 
 

Transaction question

Post by Hari » Thu, 13 Nov 2003 11:22:50


Hi,

To do this you have to set save point inside your transaction. Use the below
commands to perform,

Begin transaction <Trans name>
Save Transaction <Trans Name>
ROllback transaction <Trans Name>
Commit Tran

Refer Save transaction in BOL for more details and examples

Thanks
Hari
MCDBA


> MAB71,


> value. If there is an error, then roll back the transaction and exit from
> the stored procedure.

> Fatal errors will automatically roll back the transaction and abort the
> batch.

> Ron
> --
> Ron Talmage
> SQL Server MVP


> > I wrote a procedure in SQL 6.5 which is something like

> > CREATE  PROCEDURE  abc

> > AS

> > BEGIN TRAN xyz

> > insert statement ..

> > insert statement ...

> > update statement...

> > insert statement ...

> > insert statement ...

> > COMMIT TRAN xyz

> > I want either all statements to execute or none.

> > My question is, if an error occurs like a constraint is violated, where
> will
> > the control pass? to the next statement or will it exit the procedure?
and
> > do I need to explicitly issue a rollback tran? and if yes then where?
and
> if
> > no then will the server itself rollback the changes made prior to the
> error?

> > thx

 
 
 

1. Uncommited explicit transaction question

Hi,

I have an app that connects to SQL Server 2000 over the internet and does
transactions.  It will do this using ADO.Net.

Begin Trans

Issue 10 or 20 INSERT or update statements

Commit Trans

Sometimes what can happen is that after the begin trans and executing
several statements -- the internet connection will be lost.  So I have no
way of sending the Commit.  Now the question is, will sql server wait and
then rollback the statements issued or will it commit them.  Also how long
will it wait before taking any action.

Thanks,

Veejay Sani

2. Help with .dbx format

3. transaction question

4. How modify the form feed char?

5. Transaction question

6. Delphi32.com Your premier source for Delphi Information is back!!!

7. Stored procedure transaction question

8. Sr. Program Analyst Opening

9. Uncommited explicit transaction question

10. Transaction question

11. Another newbie different begin,rollback,commit transaction question

12. SQL: Newbie Transaction Question

13. Transaction Question