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
> 

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.

[![enter image description here][1]][1]


  [1]: https://i.sstatic.net/VSubj.png