How to use one SqlTransaction object for two different SqlConnections?

[passs]

Hello everybody!

Does anybody know how can I use the same sql transaction object for several sql connections? For example I need to copy data from Server1 to Server2 and Server3 and if somthing failed in copying then to rollback everything.
So I have:
SqlConnection connFrom = new SqlConnection(...);
SqlConnection connTo1 = new SqlConnection(...);
SqlConnection connTo2 = new SqlConnection(...);
SqlTransaction trans;
try
{
trans = connTo1.BeginTransaction();
...
// some stuff
// and here I need to change connection for this transaction
...
trans.Commit();
}
catch (SqlException ex)
{
trans.rollBack();
}
finally
{
connFrom.close();
connTo1.close();
connTo2.close();
}

How should it be done?
Thank you,
Alex

 

[B00gyeMan]

I do not think this is possible. The transaction is actually stored on the server and all you have in that SqlTransaction object is some sort of "reference" to the transaction on the server. There is no way of changing the transaction from a server to another.

 

[chiph]

You can't do it with a SqlTransaction object, but you can do it with an EnterpriseServices object (aka COM+, aka MTS).

EnterpriseServices objects implement the XA protocol, so you can commit a transaction across any number of providers that understand the two-phase commit protocol.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[passs]

Yeah, thanks.
I've already got it

Thank you,
Alex