title: Transaction count mismatch - MARS Batch is still active
body: <p>I have <code>MultipleActiveResultSets=true</code> in our c# app's connection string. I have already figured out that I can set that to <code>false</code> to prevent this error. I am trying to figure out how to modify my stored procedure to work with <code>MultipleActiveResultSets=true</code></p>
  
  <p>The stored procedure in question is a fairly simple stored procedure that executes 99% of the time from the C# app without issues. However, a few times, I have logged the following error when it executes. </p>
  
  <p><em>Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.</em></p>
  
  <p>Stored procedure code:</p>
  
  <pre><code>begin try 
  begin transaction 
      -- create @NewIDs to hold inserted IDs -- hopefully stop any issues related to @@IDENTITY 
      declare @NewIDs table (MyNewID int)
  
      insert into users       
      output inserted.id into @NewIDs
      values 
      (
          LTRIM(RTRIM(@Username)), 
          'nothing', 
          'nothing', 
          LTRIM(RTRIM(@Email)), 
          @Phone, 
          @ProfileID, 
          LTRIM(RTRIM(@FirstName)), 
          LTRIM(RTRIM(@LastName)), 
          LTRIM(RTRIM(@SearchName)),          
          @Password,           
          GETDATE()
      )
  
      declare @UserID int
      select top 1 @UserID = MyNewID from @NewIDs
  
      select @retValue = @UserID 
  
  commit transaction  
  return @retValue
  end try 
  begin catch 
      if @@TRANCOUNT &gt; 0
          rollback transaction
  
      set @retValue = 0
      return @retValue    
  end catch
  </code></pre>