VB and nested SQL Server 7 transactions-- possible? 
Author Message
 VB and nested SQL Server 7 transactions-- possible?

I've got a business object in VB (let's call it "component A") whose
transaction attributes are set "Requires New".  From it, I create
sub-components whose transactions are set to "Requires", and everything
works great.  If one of the sub-components throws an error while writing to
SQL Server, the transaction is rolled back no problem.

Now, a situation has come up where I'd like to create another sub-component
(call it "component B") from within component A.  Thing is, component B also
has it's transaction attributes set to "Requires New".  It does basically
what component A does-- creates child components whose transactional
attributes are set to "Requires", etc.

Component B usually stands alone, like component A, but a situation has come
up where it would be very nice to have "A" create an instance of "B" and
have "B" do it's thing.  Sorry for the confusing notation here.

The question I have is, can I nest these transactions in Visual Basic?  If
component B's transaction is rolled back, will it also roll back component
A's transaction?  Or are things such that the two transactions are
completely separate, and just forget about tying them in together, 'cause it
isn't possible?  I've never tried it before, and was wondering what the
rules might be, if I'm setting myself up for trouble here.  Thanks for any
and all advice.



Mon, 12 Jan 2004 23:15:36 GMT  
 VB and nested SQL Server 7 transactions-- possible?

Quote:
> The question I have is, can I nest these transactions in Visual Basic?  If
> component B's transaction is rolled back, will it also roll back component
> A's transaction?  Or are things such that the two transactions are
> completely separate, and just forget about tying them in together, 'cause
it
> isn't possible?  I've never tried it before, and was wondering what the
> rules might be, if I'm setting myself up for trouble here.  Thanks for any
> and all advice.

Hi Jim,

No, you can't perform nested transactions, the transactions are completely
separate: it is possible for B to fail and rollback but A to continue
processing and commit. What you could do, is to check the return values of
your method calls to B and abort the transaction in A if one fails. If you
really want a failure in B to abort your transaction in A, why not make B
just "Require" and transaction, rather than "Requires New"? If you want it
one way sometimes and another on different occasions, why not wrap the
method implementation in a VB Module Sub/Function and create two objects
with methods that call this common implementation, one object "Requires TX",
and one "Requires New TX"?

Regards,

Ade.



Mon, 12 Jan 2004 23:24:25 GMT  
 VB and nested SQL Server 7 transactions-- possible?
Good idea-- thanks for the help.

-Jim



Tue, 13 Jan 2004 00:38:00 GMT  
 VB and nested SQL Server 7 transactions-- possible?
JFTR, I dont know about use with mts components, but when using sql7
transactions with ado, although one cannot use nested transactions, it is
possible to simulate nested transactions using SAVEPOINTS via TSQL.

Dim C as Adodb.Connection
Set C=New Adodb.Connection
C.Open CONNECTSTRING

C.Execute "BEGIN TRAN"
C.Execute "SAVE TRAN A"
C.Execute "UPDATE TABLE1 SET X=1"
C.Execute "SAVE TRAN B"
C.Execute "UPDATE TABLE1 SET X=2"
C.Execute "SAVE TRAN C"
C.Execute "UPDATE TABLE1 SET X=3"
C.Execute "ROLLBACK TRAN B"
C.Execute "COMMIT TRAN"
Set rs=new Adodb.recordset
rs.open "SELECT X FROM TABLE1", C
debug.print rs!X

The resultant output will be 1.

SAVE TRAN LABEL and ROLLBACK TRAN LABEL can be used to roll back selective
portions of a transaction


Quote:
> Good idea-- thanks for the help.

> -Jim



Wed, 14 Jan 2004 17:36:20 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Nesting SQL Server Transactions in Forms?

2. Nested transaction in SQL Server 7

3. HELP nested transaction under ado sql server

4. Q: VB + MS SQL Server and transactions

5. SQL Transaction nesting

6. DAO, SQL Server, and Transactions

7. DAO, SQL Server, and Transactions

8. Transactions with SQL Server & ADO

9. Transactions in SQL Server 6.5

10. SQL Server Database locks during Transactions

11. Why to use COM+ transaction and not just SQL Server transaction

12. Transactions and SQL Server

 

 
Powered by phpBB® Forum Software