2

For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both servers.

According to Microsoft's BOL for BEGIN TRANSACTION:

The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

  • An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked
    server does not support the ITransactionJoin interface.

  • A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

Given the above, are there any cases where it makes sense or is mandatory to explicitly use BEGIN DISTRIBUTED TRANSACTION?

Specifically, I'm planning to call a stored procedure (that executes DML queries) on ServerB via the linked server from ServerA with the same transaction wrapped around some DML statements executed on ServerA as well. My goal is to achieve transactional consistency for both sets of DML queries happening between the two servers.

2

I think it depends on whether you want to rely on the linked server configuration option 'Enable Promotion of Distributed Transaction' or whether you want to be sure that your transaction can run only if it's distributed.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.