Two connections under one Transaction

Question

I searched araound and having some difficulties in implementing one scenario with SQL Server 2000 and sybase.

1. I have two SQL servers on different locations naming ServerA (MSSQL Server) and ServerB (Sybase server).

2. I have a table called SALARY.

3. Whenever I insert one record to customer SALARY in ServerA, I also will insert it into ServerB.

4. If should any of them failed, both of them should be rolled back.

5. I am using Asp .Net with VB for the programming.

I do not have the right to change / add table in Server B. The only thing allowed in Server B is insert/delete/update record.

I looked around and find a method to use TransactionScope but I am not quite sure it is the right way to do.

Ref: http://msdn.microsoft.com/zh-tw/library/system.transactions.transactionscope%28v=vs.80%29.aspx

Please help. THANKS!!!!

Answer 1

IMHO, TransactionScope is the right approach. However, I'm unsure if Sybase supports the ability to enlist in ambient transactions.