I am in the process of creating a stored procedure. This stored procedure runs local as well as external stored procedures. For simplicity, I'll call the local server [LOCAL] and the remote server [REMOTE].
USE [LOCAL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[monthlyRollUp]
AS
SET NOCOUNT, XACT_ABORT ON
BEGIN TRY
EXEC [REOMTE].[DB].[table].[sp]
--This transaction should only begin if the remote procedure does not fail
BEGIN TRAN
EXEC [LOCAL].[DB].[table].[sp1]
COMMIT
BEGIN TRAN
EXEC [LOCAL].[DB].[table].[sp2]
COMMIT
BEGIN TRAN
EXEC [LOCAL].[DB].[table].[sp3]
COMMIT
BEGIN TRAN
EXEC [LOCAL].[DB].[table].[sp4]
COMMIT
END TRY
BEGIN CATCH
-- Insert error into log table
INSERT INTO [dbo].[log_table] (stamp, errorNumber,
errorSeverity, errorState, errorProcedure, errorLine, errorMessage)
SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(),
ERROR_LINE(), ERROR_MESSAGE()
END CATCH
GO
When using a transaction on the remote procedure, it throws this error:
OLE DB provider ... returned message "The partner transaction manager has disabled its support for remote/network transactions.".
I get that I'm unable to run a transaction locally for a remote procedure.
How can I ensure that the this procedure will exit and rollback if any part of the procedure fails?
