SQL Transaction uncommittable while using try..catch.. Why?

Question

We're running into a problem here and I can't figure out why it behaves how it behaves.

Given the following two (simplified) stored procedures in TSQL (SQL Server 2008R2)

create procedure [datetransaction1] 
as
begin
    begin try
        begin transaction
        declare @a datetime
        exec datetransaction2 '2013-02-02 22:21', @a output
        select @a
        exec datetransaction2 '2013-020222:22', @a output
        select @a
        exec datetransaction2 '2013-02-02 22:23', @a output
        select @a

        commit transaction
    end try
    begin catch
        print 'Catch'
    end catch
end

and

create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT  
AS
BEGIN 
    BEGIN TRY
        if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
        else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
        else SET @text = replace(@text, ' ', 'T') 
        PRINT 'trydate:' + @text
        SELECT @res =convert(datetime, @text, 126)
    END TRY
    BEGIN CATCH
        PRINT ERROR_SEVERITY()
        PRINT 'errordate:' + @text
    END CATCH
END

If you then execute exec datetransaction1, we see that all 3 calls to datetransaction2 are executed, with the first and last (as expected) running correctly, and the second one entering the CATCH block within datetransaction2.

So far, so good.

But then we land in the catch block of datetransaction1 with the message that the transaction is uncommittable:

Msg 266, Level 16, State 2, Procedure datetransaction1, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Which isn't supposed to happen (I think). We caught the errors in the sub procedures, so why would the transaction suddenly become uncommittable?

Can someone explain that to me?

Note that we can probably find a way around this, but I'm intrigued more by the idea behind it. Why does this transaction suddenly become uncommittable here?

Answer 1

The reason is: SQL Server dooms the transaction WHENEVER an error occurs, whatever the error is, whether it is in a TRY block or not, whether you saved a transaction state or not, whether the error occurs in a procedure or not, whatever you do.

When the error occurs in one of the procedure calls, the transaction is doomed. You can only rollback it completely (any savepoint will not help).

At the end, since the transaction is doomed, you cannot commit it...

Try this:

SET XACT_ABORT OFF -- pityful attempt to avoid the doom
BEGIN TRANSACTION
--
-- some useful TSQL instructions could be here
--
SAVE TRANSACTION SQL_SERVER_IS_GARBAGE -- another pityful attempt to do a partial restore
PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
BEGIN TRY
  DECLARE @n int
  SELECT @n = CONVERT(int,'ABC') -- some very benign data error here (example)
  COMMIT TRANSACTION -- will never reach here
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
  PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
  IF XACT_STATE()=-1 BEGIN
    PRINT 'The transaction is doomed, say thanks to Sql Server!'
    PRINT 'CANNOT restore to the save point!'
    -- You can just cry here and abort all, you lost all the useful work
    ROLLBACK TRANSACTION
  END
  ELSE BEGIN
    -- would restore before the error if the transaction was not doomed
    ROLLBACK TRANSACTION SQL_SERVER_IS_GARBAGE -- will never reach here either!
  END  
END CATCH