I am not a fan of triggers and dynamic sql, however, what I am working with requires both.

CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN   
    SET NOCOUNT ON;
    DECLARE @DatabaseName NVARCHAR(100)
    DECLARE @FormItemID INT

    DECLARE db_cursor CURSOR FOR
    SELECT SourceID,FormItemID from Inserted

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        BEGIN TRY    
            EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE
        END TRY    
        BEGIN CATCH 
            INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) 
            SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
        END CATCH     

        FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
    END

    CLOSE db_cursor  
    DEALLOCATE db_cursor
END 

Here is what is happening.

1. An AWS DMS Replication Task is replicating data in batches of 10,000. The implementation of the replication is a black box but there appears to be nested transactions on the connections.
2. EXEC spAddEditFormItemView calls a stored procedure that exec's dynamic sql and errors out on one record.
3. The CATCH block is never executed
4. The error never makes it to AdminErrorLog.
5. The AWS Batch errors our and is never committed and the whole task fails.
6. In extended events the following sql error is caught. This is the error that failed the task. I read about conditions where commands can neither be in a commit or rollback state but truly did not fully understand that concept.

message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

severity: 16

sql_text: (@P1 int,@P2 varchar(250),@P3 int,@P4 int,@P5 int,@P6 int,@P7 varchar(500),@P8 varchar(1000),@P9 bit,@P10 varchar(50),@P11 varchar(50),@P12 varchar(50),@P13 varchar(50),@P14 int,@P15 varchar(5000),@P16 bit,@P17 int,@P18 datetime2,@P19 int,@P20 datetime2,@P21 varchar(200),@P22 int,@P23 varchar(50),@P24 varchar(50),@P25 varchar(500),@P26 int,@P27 int,@P28 varchar(200),@P29 varchar(2000),@P30 int,@P31 int,@P32 int,@P33 varchar(4000),@P34 varchar(200),@P35 bit,@P36 varchar(300),@P37 bit,@P38 varchar(50),@P39 varchar(50),@P40 varchar(100))INSERT INTO [dbo].tblFormItems values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40)

Can anyone explain why the catch block is not being caught and why the client application is getting, what my guess is, a sql exception and rolling everything back. My guess is that dynamic sql exceptions are being handled differently and the implicit trigger transaction is rolling back instead of catching the error. Also, does anyone know of a way to avoid the exception from being propagated?

I bet the safest solution would be modify the trigger to cram the sql commands into a table and then have a sql agent job look for commands to run every minute or so.

EDIT - Adding Procedure to recreate: In SSSM :

EXEC [spAddEditFormItemView] 30032,'VP_BENCHMARKING_V05'

Commands completed successfully.

Completion time: 2023-11-27T16:11:18.1762097-05:00

In the trigger it forces a rollback with a horrible error message.

ALTER PROCEDURE [dbo].[spAddEditFormItemView] ( 
    @FormItemID int,
    @Schema nvarchar(100)
)
AS
    DECLARE @SQL NVARCHAR(MAX) = 'e2e2e2e2e'
    BEGIN TRY   
        EXEC (@SQLCommand)      
    END TRY
    BEGIN CATCH
        DECLARE @X INT
    END CATCH

EDIT: I can now duplicate in SSMS.