3616 Trigger Error when Calling Stored Procedure on Linked Server

Question

3616 Trigger Error when Calling Stored Procedure on Linked Server

Old Hand

Points: 327

September 26, 2022 at 8:01 pm

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 years, 2 months ago by mrslade.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

MrRimmer Grasshopper Points: 12 More actions · Answer 2

When I had a similar situation, I used Service Broker to ensure that any processing the trigger performed did not interact with code that raised the trigger i.e. the trigger fires a sp which puts a message into a queue. An activator sp then picks up the message and does any work necessary, asynchronously to the client.