2 DBs connections + commit/rollback

  • Thread starter Thread starter Paulo Roberto
  • Start date Start date
P

Paulo Roberto

Hi, the asp.net app have to communicate with MSSQL and FireBird DBs. Because
FB is desktop and MSSQL is web. Firebird is 3rd comp. .net driver we use...

but the situation:

Open FB tran and MSSQL tran... If the commit fails on FB that is first
executed, the MSSQL (2nd step) is not executed, no problem...

but how can I do a FB rollback that was commited if the MSSQL fails on
commit?

Have you ever seen this?

Thanks
 
Paulo said:
Hi, the asp.net app have to communicate with MSSQL and FireBird DBs. Because
FB is desktop and MSSQL is web. Firebird is 3rd comp. .net driver we use...

but the situation:

Open FB tran and MSSQL tran... If the commit fails on FB that is first
executed, the MSSQL (2nd step) is not executed, no problem...

but how can I do a FB rollback that was commited if the MSSQL fails on
commit?

Have you ever seen this?

Thanks

You want a distributed transaction, also known as two-phase commit,
which uses MSDTC.

I believe that requires something like this...

http://www.ibprovider.com/eng/documentation/firebird_adonet/firebird_adonet.html#transaction_scope

HTH
 
Paulo Roberto said:
Hi, the asp.net app have to communicate with MSSQL and FireBird DBs.
Because FB is desktop and MSSQL is web. Firebird is 3rd comp. .net driver
we use...

but the situation:

Open FB tran and MSSQL tran... If the commit fails on FB that is first
executed, the MSSQL (2nd step) is not executed, no problem...

but how can I do a FB rollback that was commited if the MSSQL fails on
commit?

You can't, at least not with built in stuff.

If you need a transaction that spans two different servers, and cannot link
them so there is one call, you have to create your own transaction
mechanism. One kludge is to have two transactions and not call final commit
until both are reading success. You can then just call rollback when you
have a failure. You end up having to watch the steps a bit closer for
failure, of course, but it can be done this way. Good luck!

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************
 
Back
Top