ADO err with trans begin/commit ? 
Author Message
 ADO err with trans begin/commit ?

Hi
when I use ADO to run stored procedure should I put begin trans and commit
inside stored procedure or in VB program?
Also I am thinking to use trans begin/ commit in case there is database shut
down or network disconnection, so that when VB program get an error of ADO
connection object closed during operation, database don't commit but
rollback changed records. Is this usual case to use trans begin/commit? What
other usual issues are there to use trans begin/commit or ADO error ?

Also is there a book that explains well?

Thank you very much.



Tue, 13 Apr 2004 04:17:00 GMT  
 ADO err with trans begin/commit ?


Quote:
> Hi
> when I use ADO to run stored procedure should I put begin trans and commit
> inside stored procedure or in VB program?

As a general rule, I like to see as much logic as possible centralised on
the server. Therefore, I would recommend implementing your
<begin..commit..rollback> logic within the stored procedure. Of course this
is a generalisation, and an obvious exception would be if you want to "wrap"
the execution of more than one stored procedure into a transaction (in which
case you could do it on the client-side). But even in that case, you could
still implement the logic sever-side.

I always place this logic within Stored Procedures - remembering to
incorporate an error handler that explicitly does the RollBack.

The only overriding reason I can see for implementing transactions with ADO,
would be if you weren't sure how the stored procedures were constructed, and
wanted to make sure that a transaction was being implemented. Then it gives
you a good "safety net".

  Regds.
Robert.

Quote:
> Also I am thinking to use trans begin/ commit in case there is database
shut
> down or network disconnection, so that when VB program get an error of ADO
> connection object closed during operation, database don't commit but
> rollback changed records. Is this usual case to use trans begin/commit?
What
> other usual issues are there to use trans begin/commit or ADO error ?

> Also is there a book that explains well?

> Thank you very much.



Tue, 13 Apr 2004 04:27:22 GMT  
 ADO err with trans begin/commit ?
When you have trans begin/commit in a stored procedure, does it rollback
when ADO connection between sql server and vb program is disconnected by
database or server shutdown or network failure.

What book or what website do you get these kind of application design
knowledge?

Thank you very much.


Quote:



> > Hi
> > when I use ADO to run stored procedure should I put begin trans and
commit
> > inside stored procedure or in VB program?

> As a general rule, I like to see as much logic as possible centralised on
> the server. Therefore, I would recommend implementing your
> <begin..commit..rollback> logic within the stored procedure. Of course
this
> is a generalisation, and an obvious exception would be if you want to
"wrap"
> the execution of more than one stored procedure into a transaction (in
which
> case you could do it on the client-side). But even in that case, you could
> still implement the logic sever-side.

> I always place this logic within Stored Procedures - remembering to
> incorporate an error handler that explicitly does the RollBack.

> The only overriding reason I can see for implementing transactions with
ADO,
> would be if you weren't sure how the stored procedures were constructed,
and
> wanted to make sure that a transaction was being implemented. Then it
gives
> you a good "safety net".

>   Regds.
> Robert.

> > Also I am thinking to use trans begin/ commit in case there is database
> shut
> > down or network disconnection, so that when VB program get an error of
ADO
> > connection object closed during operation, database don't commit but
> > rollback changed records. Is this usual case to use trans begin/commit?
> What
> > other usual issues are there to use trans begin/commit or ADO error ?

> > Also is there a book that explains well?

> > Thank you very much.



Tue, 13 Apr 2004 05:53:36 GMT  
 ADO err with trans begin/commit ?


Quote:
> When you have trans begin/commit in a stored procedure, does it rollback
> when ADO connection between sql server and vb program is disconnected by
> database or server shutdown or network failure.

This excerpt from sql server books online should answer that question: -

" Even if the server hardware, operating system, or SQL Server itself fails,
SQL Server uses the transaction logs, upon restart, to automatically roll
back any uncompleted transactions to the point of the system failure. "

I think you want to review transactions in BOL, in particular take a look at
the discussions on "nested" transactions. This is important because if
you're explicitly BEGINing transactions with ADO, any BEGIN..COMMIT logic
within the SPs that you are executed is, presumably, treated as a nested
transaction....

Quote:

> What book or what website do you get these kind of application design
> knowledge?

BOL is a good place to get the facts, but unfortunately it's a bit like
trying to learn a language by reading a dictionary .... Why don't you check
out Microsoft Press ( on the web ) and see if you can find a good SQL
Server/ADO reference. Bill Vaughn wrote one, but I'm not sure to what extent
he deals with transactions.....

HTH
Robert.

Quote:
> Thank you very much.





> > > Hi
> > > when I use ADO to run stored procedure should I put begin trans and
> commit
> > > inside stored procedure or in VB program?

> > As a general rule, I like to see as much logic as possible centralised
on
> > the server. Therefore, I would recommend implementing your
> > <begin..commit..rollback> logic within the stored procedure. Of course
> this
> > is a generalisation, and an obvious exception would be if you want to
> "wrap"
> > the execution of more than one stored procedure into a transaction (in
> which
> > case you could do it on the client-side). But even in that case, you
could
> > still implement the logic sever-side.

> > I always place this logic within Stored Procedures - remembering to
> > incorporate an error handler that explicitly does the RollBack.

> > The only overriding reason I can see for implementing transactions with
> ADO,
> > would be if you weren't sure how the stored procedures were constructed,
> and
> > wanted to make sure that a transaction was being implemented. Then it
> gives
> > you a good "safety net".

> >   Regds.
> > Robert.

> > > Also I am thinking to use trans begin/ commit in case there is
database
> > shut
> > > down or network disconnection, so that when VB program get an error of
> ADO
> > > connection object closed during operation, database don't commit but
> > > rollback changed records. Is this usual case to use trans
begin/commit?
> > What
> > > other usual issues are there to use trans begin/commit or ADO error ?

> > > Also is there a book that explains well?

> > > Thank you very much.



Thu, 15 Apr 2004 22:24:48 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. begin trans - commit trans

2. BEGIN,COMMIT Trans

3. Begin Trans..Commit with Client Access ?

4. Begin and commit trans

5. ADO Begin/Commit Transactions

6. Using BeginTrans, Commit Trans

7. Use of Begin/End trans scope

8. Help with transactions using Begin Trans, Edit, and CommTrans

9. Help with transactions using Begin Trans, Edit, and CommTrans

10. Begin trans / open a record set on a stored proc on sql server

11. Begin-Rollback-Commit Transaction

12. Workspace Transaction Begin/Commit/Rollback bug?

 

 
Powered by phpBB® Forum Software