Cross-database transactions from one SP

Question

I need to update multiple databases with a few simple SQL statement. The databases are configurared in SQL using 'Linked Servers', and the SQL versions are mixed (SQL 2008, SQL 2005, and SQL 2000). I intend to write a stored procedure in one of the databases, but I would like to do so using a transaction to make sure that each database gets updated consistently.

Which of the following is the most accurate:

1. Will a single BEGIN/COMMIT TRANSACTION work to guarantee that all statements across all databases are successful?
2. Will I need multiple BEGIN TRANSACTIONS for each individual set of commands on a database?
3. Are transactions even supported when updating remote databases? I would need to execute a remote SP with embedded transaction support.

Note that I don't care about any kind of cross-database referential integrity; I'm just trying to update multiple databases at the same time from a single stored procedure if possible.

Any other suggestions are welcome as well. Thanks!

Answer 1

You should be able to accomplish #1 using a distributed transaction. You will need DTC active and you'll need to use BEGIN DISTRIBUTED TRANSACTION along with ROLLBACK TRANSACTION and COMMIT TRANSACTION within your stored procedure.

Dealing with the DTC can being up a lot of gotchas, so good luck :)