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.
1条答案
按热度按时间cbjzeqam1#
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