title: Trigger Error: The current transaction cannot be committed and cannot support operations that write to the log file
body: <p>So I am getting the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get the error: </p>
  
  <pre><code>The current transaction cannot be committed and cannot support operations that write to the log file. 
  </code></pre>
  
  <p><strong>Any ideas on what I am doing wrong? (this is just a sample that I created to mimic the problem so please no "why are you doing this?", "this has security implications", etc..)</strong></p>
  
  <hr>
  
  <p>So my table looks like:</p>
  
  <pre><code>CREATE TABLE tSOMETABLE
  (  
      RecID INT NOT NULL IDENTITY(1,1)
      Val VARCHAR(20),
  CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED 
  (
      RecID ASC
  )
  )
  </code></pre>
  
  <p>So in my trigger I have:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT]     
      ON [dbo].[tSOMETABLE]   
      FOR INSERT  
  AS      
  SET NOCOUNT ON  
  BEGIN   
           BEGIN
              SELECT * INTO #temp FROM INSERTED
  
              WHILE EXISTS (SELECT 1 FROM #temp)
              BEGIN
                  DECLARE @RecID INT      
                  SELECT @RecID = RecID
                  FROM #temp t
                  EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID
                  DELETE #temp WHERE @RecID = RecID
              END         
          END   
  END
  </code></pre>
  
  <p>Now the code of sp_SomeProc looks like:</p>
  
  <pre><code>CREATE PROC sp_SomeProc 
  (
      @EventType VARCHAR(50),
      @RecID INT,
      @Debug BIT = 0
  )
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @ProcTable TABLE 
      (
          RecID INT NOT NULL IDENTITY(1,1),
          Cmd VARCHAR(MAX)
      )
  
      INSERT INTO @ProcTable(Cmd)
        SELECT 'EXEC sp_who'
        UNION
        SELECT 'EXEC sp_SomeStoredProcThatDoesntExist'
  
  
      DECLARE  @RecID INT  
      SELECT @RecID = MIN(RecID) FROM @ProcTable
      WHILE @RecID IS NOT NULL
      BEGIN  
          DECLARE @sql VARCHAR(MAX)
          SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID
          IF @Debug = 1
              PRINT @sql
          ELSE
              BEGIN
                  BEGIN TRY      
                      EXEC(@sql)
                  END TRY
                  BEGIN CATCH
                      DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX)
                      SELECT @Msg = 'Failed While Executing: ' + @sql  
                      SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE()
                      -- DO SOME MORE STUFF HERE AND THEN ...
                      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
                  END CATCH 
              END
          SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID &gt; @RecID
      END  
  END
  </code></pre>
  
  <p>So to test I try:</p>
  
  <pre><code>INSERT INTO tSOMETABLE(Val)
  SELECT 'Hello'
  </code></pre>