0

I have a linked Server connected from ServerA to ServerB. In each server there is an SP that initiates a transaction and it is necessary for one ServerA.SP to be executed within ServerB.SP. My problem is that when doing that, I get the error:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

I have searched for information about that error but the questions/answers do not include linkedServers. That's why I'm here. The code is something like that, its really simple:

CREATE OR ALTER PROCEDURE #DB1_sp
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION 
            EXEC [LinkedServer].[db].[Schema].[SP]
                ... params ...
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        EXEC [Logs].[SetError]
    END CATCH
END
GO

EXEC #DB1_sp

On the LinkedServer side, the SP is almost the same code. In fact all my code works if we remove the BEGIN TRANSACTION, but that is not an option due to the initial validations.

I have tried looking into DISTRIBUTED TRANSACTIONS, SAVE TRANSACTIONS and statements like SET XACT_ABORT ON all of that in the top stored procedure (because I can't access/edit the inner SP) but it doesn't work.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Moises Hernandez
  • 11
  • 2
  • The error is clear, there is a flow through your code which has a mismatched number of begin/commit/rollback. Without seeing all of your code we can't really advise. – Dale K Apr 12 '23 at 23:31
  • If there was a distributed transaction related problem the error would notify about it accordingly.Have you tried a pass-through query to check how the remote procedure behaves when executed in a transaction (unless you have access to the linked server and you could execute it locally, there)? `EXECUTE ('BEGIN TRANSACTION; EXEC [db].[Schema].[SP]... params ...; COMMIT TRANSACTION;') AT [LinkedServer];` – lptr Apr 13 '23 at 00:06
  • What's the code for other SP? are both servers sql servers? – siggemannen Apr 13 '23 at 09:40

1 Answers1

0

To use transaction over different instances of SQL Server or other RDBMS you have to activate MSDTC on all machines that executes SQL Server. This is knows has "two phase commit". The transaction coordinator will initiate a voting phase on all server that are concerned by the transaction and if all are OK, the a SQL COMMIT order is sent to all the machines. The second phase consist to apply the effective COMMIT on all machines...

BUT if one machine has crashed between phase 1 (vote) and phase 2 (apply) the the transtation is committed partially on the alive machines and not on this that is down.

The results will be a find mess and I wish you good luck in sorting out the valid data and the inconsistencies and restoring the situation

SQLpro
  • 3,994
  • 1
  • 6
  • 14