3616 Trigger Error when Calling Stored Procedure on Linked Server

  • I am updating a proprietary database using an SSIS package. From a web UI a user clicks a button to start the update using SQL stored procedures and I am trying to generate feedback from the process so they know if the update was successful. On sql instance 1 (HTSRV1) I have the table where an insert is created when the button is pushed by the user with the following insert code.

    DECLARE @SyncId UNIQUEIDENTIFIER
    SET @SyncId = NEWID()

    select @SyncId

    INSERT INTO [pr].[bctrSync]
    ([SyncId]
    ,[dbid]
    ,[InitiatedBy]
    ,[InititatedDateTime]
    ,[SyncStatus]
    ,LastUpdated)
    VALUES
    (@SyncId
    ,'ABC'
    ,[User:Userid]
    ,getdate()
    ,10
    ,getdate()
    )
    GO

    An AFTER Insert trigger executes a stored procedure on a linked server to run the SSIS package. The linked server only has the SSISDB.

    BEGIN
        SET XACT_ABORT OFF
        declare @_syncinstance nvarchar(50);
    set @_syncinstance = (select i.Syncid from bCtrSync a inner join inserted i on a.SyncId = i.SyncId)

        BEGIN TRY  
            set @_syncinstance = (select i.Syncid from bCtrSync a inner join inserted i on a.SyncId = i.SyncId)
           exec [HTSRV2].[ssisdb].[dbo].[exec_ssis_tmEmpLeaveADP] @syncinstance = @_syncinstance
        END TRY  

        BEGIN CATCH

        END CATCH
        SET XACT_ABORT ON

    END

    The stored procedure on the linked server is as follows;

    BEGIN
    BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON
    declare @execution_id bigint

    BEGIN TRANSACTION
    EXECUTE AS LOGIN = 'xxx\xx'
    exec ssisdb.catalog.create_execution
    @folder_name = 'CPA'
    ,@project_name = 'tmEmpLeaveADP'
    ,@package_name = 'UpdateEmpCF.dtsx'
    ,@execution_id = @execution_id output
    ,@use32bitruntime=True
    ,@reference_id=Null

    Select @execution_id

    DECLARE @var0 smallint = 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'SyncInstance', @parameter_value=@syncinstance
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
    --REVERT
    COMMIT TRANSACTION
    END TRY

    BEGIN CATCH

    INSERT INTO [HTSRV1].[CPA].[pr].[bctrDBErrors]
    (UserName
    ,ErrorNumber
    ,ErrorState
    ,ErrorSeverity
    ,ErrorLine
    ,ErrorProcedure
    ,ErrorMessage
    ,ErrorDateTime)
    VALUES
    (SUSER_SNAME(),
    ERROR_NUMBER(),
    ERROR_STATE(),
    ERROR_SEVERITY(),
    ERROR_LINE(),
    ERROR_PROCEDURE(),
    ERROR_MESSAGE(),
    GETDATE());
    END CATCH;

    END

    I have set up a login (SQL Authentication not AD) with access to both sql instances. I can log into either sql instance (SSMS) with that login in and manually execute the stored procedure to perform the update. When I try and execute the sp via the trigger I get the following error message;

    "Msg 3616, Level 16, State 1, Line 11

    An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back."

    I have tried to search why this is happening and found a post that explained that any trigger errors needed to be rolled back before so I added the code in the CATCH statement.

        BEGIN CATCH
    IF XACT_STATE() = -1 ROLLBACK
    END CATCH

    This generated  the error msg;

    Msg 3609, Level 16, State 1, Line 11

    The transaction ended in the trigger. The batch has been aborted.

    Searching on this error lead me to believe that the trigger should not the Rollback statement.

    I can create a new database on the second server and have all my message logging tables for the process on one database, but I would like to understand what is happening with the transactions and the commit and if possible how to correct.

     

    • This topic was modified 2 months, 1 week ago by  mrslade.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply