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!