Transaction across 2 DB platforms

[mwa]

I have function in an ASP.net application that performs an insert into 2 different database platforms... One inserts into a SQL Server database, and a second into a DB2 database. The problem is that if either of the inserts fail, I need to rollback both.

I've only ever done transactions with multiple steps to the same DB platform. Is it possible to setup a transaction to work across 2 DB platforms? If so, how would I approach this?

Thanks for any assistance.

mwa
<><

 

[jbenson001]

As far as I know, transactions are per DB. So, you will have to check after each insert. If say, the last one fails, then you will have to delete the inserted rows in the previous steps.

 

[RiverGuy]

I've never attempted it, but I would think you could do this if you're using a DB2 provider which supports transactions. Here's some pseudocode:

Dim SQLFail As Boolean = False
Dim DB2Fail As Boolean = False
Try
  SomeSQLCommand.ExecuteNonQuery()
Catch Ex As Exception
  SQLFail = True
End Try

If SQLFail = False 'Execute DB2 query if SQL query was successful
  Try
    SomeDB2Command.ExecuteNonQuery()
  Catch Ex As Exception
    DB2Fail = True
  End Try
End If

If SQLFail = False AndAlso DB2Fail = False Then
  Try
    SomeSQLTransaction.Commit()
  Catch
  End Try
  Try
    SomeDB2Transaction.Commit()
  Catch
  End Try
End If

If SQLFail = True Or DB2Fail = True Then
  Try
    SomeSQLTransaction.Rollback()
  Catch
  End Try
  Try
    SomeDB2Transaction.Rollback()
  Catch
  End Try
End If