All geek questions in one place

Single SP internetwork transactions

I need to update several databases with a few simple SQL statements. Databases are configured in SQL using "Linked Servers", and 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 this using a transaction to make sure that each database is updated sequentially.

Which of the following is most accurate:

  • Will one BEGIN / COMMIT TRANSACTION work to ensure that all applications in all databases are successful?
  • Do I need several BEGIN TRANSACTIONS for each individual set of commands in the database?
  • Are transactions supported when updating remote databases? I will need to run a remote SP with built-in transaction support.

Please note: I do not care about any referential integrity of cross-databases; I'm just trying to upgrade multiple databases at the same time from the same stored procedure, if possible.

Any other suggestions are welcome. Thank!

+3
source share
2 answers

You can execute # 1 using a distributed transaction. You will need an active DTC, and you will need to use BEGIN DISTRIBUTED TRANSACTION along with ROLLBACK TRANSACTION and COMMIT TRANSACTION in your stored procedure.

Working with DTC can be very difficult, so good luck :)

+1

. BEGIN DISTRIBUTED TRANSACTION DTC, , , . . Transact-SQL MSDN.

, . , DQ ( ) . 99,5% (.. 43 ), 5 , 97,5% (216 ). 10 95% (428 ). , , SP (, ..), .

, DQ Service Broker.

+4

Source: https://habr.com/ru/post/1748037/

More articles:


All Articles