Sql-server – Transaction Error in TRY CATCH block

rollbacksql serversql-server-2008-r2transaction

The following code returns this message in case something goes wrong:

Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements. Previous count = 0, current count = 1.

OPEN extensionViews
     FETCH NEXT FROM extensionViews
     INTO @Viewname
     WHILE @@FETCH_STATUS = 0
     BEGIN      
        BEGIN TRY
                exec sp_refreshview @Viewname
        END TRY
        BEGIN CATCH
            print 'ERROR sp_refreshview'
        END CATCH 

        FETCH NEXT FROM extensionViews
        INTO @Viewname
     END
     CLOSE extensionViews
     DEALLOCATE extensionViews

I already found out that there is something going wrong with the implicit transactions, but I do not really understand it entirely.

In case of a CATCH, do I have to explicitly ROLLBACK the transaction? Does this mean that I have to start the transaction explicitly as well insode of the Cursor to rollback THIS one command instead of all?

Best Answer

Given the following info:

Previous count = 0, current count = 1.

the implication is that sp_refreshview is opening an explicit transaction but encountering an unhandled exception such that it doesn't do its own ROLLBACK. The "Previous count = 0" means that your code is not creating the Transaction.

This is not something that you can fix in your code because a subprocess must exit with the same @@TRANCOUNT that it started with. I doubt you will be able to add a ROLLBACK, or even a BEGIN TRAN and then ROLLBACK to help this.

At the very least, you need to figure out which View is causing the problem. You can do that by printing the view name being given to that system stored procedure before executing sp_refreshview, especially if the error is one of the few that cannot be trapped by the TRY...CATCH construct. Just add the following statement just after the BEGIN TRY:

RAISERROR(N'View: %s', 10, 1, @ViewName) WITH NOWAIT;

Also, it would be helpful to see the exact error message, so replace the PRINT in the CATCH block with the following query:

SELECT @Viewname AS [ViewName],
       ERROR_NUMBER() AS [ErrorNumber],
       ERROR_MESSAGE() AS [ErrorMessage];

You can also add the following to the CATCH block, but I doubt it will solve this issue:

IF (@@TRANCOUNT > 0)
BEGIN
  ROLLBACK;
END;

P.S. You should be using sysname (all lower-case) for variable and columns that hold SQL Server object names since that is the datatype used in the system tables for object names. sysname is an alias for NVARCHAR(128).

Related Solutions

Sql-server – Rollback group of DDL statements

The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error to something like print 'HELLO!'; in that case, you will NOT see 'HELLO!' printed; you will see the error instead. Books Online has a list of cases of errors which you cannot catch.

An alternative here is to SET XACT_ABORT ON before you begin your transactions. Then, you can rollback your changes upon getting your first error.

Sql-server – Transaction and Try-catch in SQL Server Job

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie. your catch should re-raise).

Best Answer

Related Solutions

Sql-server – Rollback group of DDL statements

Sql-server – Transaction and Try-catch in SQL Server Job

Related Question