1

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?

  • Is there any caller of this code? – Ashwini Mohan Sep 22 '16 at 9:46
  • yes, sure, this code is part of a procedure. – Magier Sep 22 '16 at 10:44
  • Do you actually issue a BEGIN TRAN earlier in the code? – Solomon Rutzky Sep 22 '16 at 13:47
  • Srutzky the whole thing is called from an ssdt Post Deployment Script. – Magier Sep 29 '16 at 20:43
2

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).

  • After your assumption about sp_refreshview having an implementaion that may cause that behaviour, I looked around and voila, there I found a thread having the same issues with this SP: social.msdn.microsoft.com/Forums/sqlserver/en-US/… So, I will give the other answers a try (same workaournds show up in that thread) and as soon they fail, I might have to look for another solution. – Magier Sep 22 '16 at 15:24
  • 1
    @Magier Please see the change I made to my answer. I added a RAISERROR to just before the call to sp_refreshview so that the name will print each time. – Solomon Rutzky Sep 22 '16 at 20:08
1

It works fine for me, you should also try.

DECLARE @Viewname VARCHAR(150)

--declare @ViewFullname varchar(150)

DECLARE extensionViews CURSOR FOR SELECT NAME FROM SYS.views
OPEN extensionViews
     FETCH NEXT FROM extensionViews
     INTO @Viewname
     WHILE @@FETCH_STATUS = 0
     BEGIN      
        BEGIN TRY
                exec sp_refreshview @Viewname  -- I think you are passing wrng input to this SP. Your are fetching data to @Viewname and passing @ViewFullname as input
        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 = 0 
                rollback
            if @xstate = 1 
            rollback;

            print 'ERROR sp_refreshview'
        END CATCH 

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

You can visit this link to know more about XACT_STATE() https://technet.microsoft.com/en-us/library/ms189797.aspx

Thanks

  • TY, I fixed the ViewName question error you pointed out - You are handling if @xstate = 1 twice, is that your intention?? – Magier Sep 22 '16 at 10:33
  • There should be 0 actually, I edited the script. Thanks ! – Rajesh Ranjan Sep 22 '16 at 13:41
  • 1
    If all 3 IF conditions checking @xstate do the same thing, you might as well not even check it and just issue a ROLLBACK with no IF condition around it. – Solomon Rutzky Sep 22 '16 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.