SQLTransaction Question...

[IAC*77]

I am writing an application that writes to two separate SQL Servers so I use two separate SQLConnection objects. Problem is, I would like to use a nested transaction so that if the parent transaction is rolled back then the inner transaction is also rolled back as below:

-----------------------------------------------------------

Declare Connection1
Declare Connection2

Open Connection1

Open Connection2

Begin Transaction For Connection1

Update database for Connection1

Begin Transaction For Connection2

Update database for Connection2

Commit Transaction For Connection2

Rollback Transaction For Connection1 (I want this to rollback the data for Connection1 and Connection2)

-----------------------------------------------------------

Clearly the above does not work due to the transactions being associated with different connection objects. Is there any way this can be achieved in VB.NET?

[mendhak]

How about this:

Dim trans As SqlTransaction = conn.BeginTransaction()
 
        Dim trans2 As SqlTransaction = conn2.BeginTransaction()
 
Try
       'Your operations
 
Catch sqlx as SQLException
 
'handle it
       trans.Rollback()
       trans2.Rollback()
 
Finally
 
       trans.Commit()
       trans2.Commit()
 
End Try

[IAC*77]

Well that would work, problem is, the two connections are in different DLLs (I probably should have mentioned that).

Now I know I could "botch it" by exposing a method in the nested transaction DLL which could be used by the parent transaction DLL to call the commit/rollback remotely. I was just hoping that there was some other way.

[alextyx]

Dear IAC77, I'm a lucky man, because I have to treat more simple problem than yours, but I'm interested on your question and on the ways we can solve it.....if possible!
Mendhak give us a good suggestion but I'm trying to figure what happens if:

trans.Commit()

---- System Crash, here according with Murphy's law ---------

trans2.Commit()

What happens to Trans2?

[IAC*77]

alextyx,

That's a valid question, something I've never got round to finding out. I believe the data would be rolled-back (for trans2 only) by the server due to the connection being dropped when the application crashed. It seems very unlikely that the server would auto-commit the data.

[maged]

i dont know, may be it's too late but

Mendhak: i think your code is not right, why ? because the finally block is executed anyway whether there is an error or not. so in the case of exception the transaction will be rolledback then trying to execute the commit in the finally block (which will be executed anyway). the finally block itself will raise exception. so i think the commit should be moved from the finally to the last line of the try block. so that if it can't commit then rollback

i know i am late , but i was searching for some information on the forums and i mbumbed out with this thread.

one other thing. Mendhak is far more advanced than me, so i think it is just a typing mistake.

rgds

[mendhak]

Ah, that was from my early VB days

Although this thread is more than a year old and completely irrelevant, I think I'd correct that post like this:

Dim trans As SqlTransaction = conn.BeginTransaction()
 
        Dim trans2 As SqlTransaction = conn2.BeginTransaction()
 
Try
       'Your operations
 
trans.Commit()
       trans2.Commit()
 
Catch sqlx as SQLException
 
'handle it
       trans.Rollback()
       trans2.Rollback()
 
Finally
 
       
 
End Try

This way any error will roll back both transactions. Of course, there may have to be checks in place to rollback the other transaction which might not even occur, so a boolean If-Then-Else can be placed there.