Transactions Between two Databases

• Mark-459099

  SSC Eights!

  Points: 911

  More actions

  September 30, 2008 at 5:11 pm

  #191562

  I wonder if anyone can help me with this problem:

  I have a sql server database and a oracle database. I need to perform a stored procedure that fires on a table in sql server and then fires on a table in oracle.

  I first need to join the tables from each database first, then perform a transaction on the joined database, then have the databases essentially "break" with the new update info and each table return to their respective databases. I want the transaction to be "all or nothing", so that if something fails, neither the Oracle table or the Sql table will be affected.

  I also may need to join these two tables again to perform a SELECT statement to get the new results.

  Is this possible? Or is there a more simpler way?

  Any help would be appreciated.

  Thanks a lot!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  October 1, 2008 at 6:52 am

  #879436

  You may want to check this... http://articles.techrepublic.com.com/5100-10878_11-1054237.html

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.