Linked Server COMMIT TRANSACTION fail on TRY CATCH

Question

i'm working with Linked Server, and this code working fine

BEGIN TRY
    INSERT INTO [Dev].[dbo].tb_test (no)  SELECT no from [MYLINKEDSERVER].[mydb].[dbo].tb_test 
    DELETE FROM [MYLINKEDSERVER].[mydb].[dbo].tb_test
END TRY
BEGIN CATCH
    SELECT 'fail'
END CATCH

but when i use it with COMMIT TRANSACTION, like this

BEGIN TRY
    BEGIN TRANSACTION 
    INSERT INTO [Dev].[dbo].tb_test (no)  SELECT no from [MYLINKEDSERVER].[mydb].[dbo].tb_test 
    DELETE FROM [MYLINKEDSERVER].[mydb].[dbo].tb_test
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

show error

OLE DB provider "SQLNCLI11" for linked server "MYLINKEDSERVER" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

What is wrong?

Answer 1

The solution is to write a stored procedure on remote server with return value, and execute it locally:

BEGIN TRY
BEGIN TRANSACTION 
    INSERT INTO [Dev].[dbo].tb_test (no)  
        SELECT no 
        FROM [MYLINKEDSERVER].[mydb].[dbo].tb_test 

    DECLARE @returnvalue INT

    EXEC @returnvalue = [MYLINKEDSERVER].[mydb].[dbo].sp_update @no

    IF @returnvalue = 1
    BEGIN
        COMMIT
    END
    ELSE 
    BEGIN
        ROLLBACK
    END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

The stored procedure:

CREATE PROCEDURE [dbo].[sp_update] 
     @no NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY
        BEGIN TRANSACTION
            DELETE FROM mytb 
            WHERE no = @no

            COMMIT
            RETURN 1
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
        RETURN 0
    END CATCH
END

Hope help someone