Transaction In Transaction 
Author Message
 Transaction In Transaction

Hi,

I'm just wondering how does MTS handle transaction if there are transaction
code in stored procedure.

Say, if I have the following objects:

One MTS object (MTSA) that requires transaction;
One MTS object (MTSB) that supports transaction;
One Stored Procedure (SP) that within itself calls the BEGIN TRANSACTION and
COMMIT TRANSACTION statements.

Now,
MTSA calls MTSB
MTSB calls SP
SP begins a transaction
SP commits a transaction
Back in MTSB, some error happens
MTSB SetAbort
MTSA SetAbort

Now, will the transaction that was commited by SP be rolled back or still
commited?

Thank you.

Conax



Sat, 13 Mar 2004 08:05:35 GMT  
 Transaction In Transaction
It depends upon how you code the components and what settings you
choose in the MTS environment.  If what you are asking is can the SP be
rolled back if a component that ran it (or the parent component of that
component) aborts, then yes -- assuming your SP is on a DTC compatible
resource management DBMS like SQL Server.


Quote:
> Hi,

> I'm just wondering how does MTS handle transaction if there are transaction
> code in stored procedure.

> Say, if I have the following objects:

> One MTS object (MTSA) that requires transaction;
> One MTS object (MTSB) that supports transaction;
> One Stored Procedure (SP) that within itself calls the BEGIN TRANSACTION and
> COMMIT TRANSACTION statements.

> Now,
> MTSA calls MTSB
> MTSB calls SP
> SP begins a transaction
> SP commits a transaction
> Back in MTSB, some error happens
> MTSB SetAbort
> MTSA SetAbort

> Now, will the transaction that was commited by SP be rolled back or still
> commited?

> Thank you.

> Conax

Tim


Sat, 13 Mar 2004 11:28:03 GMT  
 Transaction In Transaction
You have a very clear understanding on the work flow. You must use the
ObjectContext to accomplish the goal. Interestingly it even works across the
multiple databases(such as Oracle, SQL server, Access).

Here is the flow you can use:

Dim objMTSA As MTxAS.ObjectContext
Dim objMTSB As dllprojectname.MTSBObject

'Create Transactor in my context
 Set objMTSA = GetObjectContext
 Set objMTSB = objMTSA.CreateInstance("dllprojectname.MTSBObject")

now you can call method of objMTSB like:

objMTSB.DOSomething(argument)
The DoSOmething must be built on ObjectContext model as well.

Example:
Public Function DoSOmething(ByVal strFirstName As String) as Boolean
On Error GoTo AddCustomerErr
    'Variables
    Dim objContext As MTxAS.ObjectContext

    'Get Object Context
    Set objContext = GetObjectContext()

    'Open Connection
    Dim objConnection As ADODB.Connection
    Set objConnection = New ADODB.Connection

    objConnection.Provider = "SQLOLEDB"
    objConnection.ConnectionString = "set you conn string"
    objConnection.Open

    objConnection.Execute "EXECUTE sp_AddCustomer  strFirstName

    'Tell MTS we succeeded
    objContext.SetComplete
    DoSOmething=True
AddCustomerExit:
    Exit Function

AddCustomerErr:
    'tell MTS we failed
    objContext.SetAbort

    Resume AddCustomerExit

End Function

Now, you are in a position to tell if the DoSOmething is succeeded or not.
If the DoSOmething is succeeded then
objMTSA.SetComplete
else
objMTSA.SetAbort

Hope this will help. Good luck.


Quote:
> Hi,

> I'm just wondering how does MTS handle transaction if there are
transaction
> code in stored procedure.

> Say, if I have the following objects:

> One MTS object (MTSA) that requires transaction;
> One MTS object (MTSB) that supports transaction;
> One Stored Procedure (SP) that within itself calls the BEGIN TRANSACTION
and
> COMMIT TRANSACTION statements.

> Now,
> MTSA calls MTSB
> MTSB calls SP
> SP begins a transaction
> SP commits a transaction
> Back in MTSB, some error happens
> MTSB SetAbort
> MTSA SetAbort

> Now, will the transaction that was commited by SP be rolled back or still
> commited?

> Thank you.

> Conax



Sat, 13 Mar 2004 21:44:16 GMT  
 Transaction In Transaction
Thanks guys for your replies.

Conax



Sun, 14 Mar 2004 08:16:52 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. To Transaction or Not to Transaction that is the question

2. ADODB TRANSACTION ADO ERROR No Transaction is Active

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

4. Reqiured Transaction and Required New Transaction

5. Cannot connect to the transaction manager or the transaction (-2147168229)

6. ADODB TRANSACTION ADO ERROR No Transaction is Active

7. ADO Connection Transaction vs. ObjectContext Transaction

8. ADOConnection.Transaction vs. ObjectContext.Transaction

9. When to use MTS transactions and ADO Transactions

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

11. ADO and Transaction in SQL Server 7.0: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

12. Getting run-time error #8000FFFF ("Illegal Operation while in a transaction server component transaction")

 

 
Powered by phpBB® Forum Software