Avatar of RICuser
Flag for United States of America asked on

Conditional TRY..CATCH block

Hello Experts,
I am trying to include some logic in a maintenance job step (MS SQL Server 2005-2008)
The job step executes a daily maintenance stored procedure in the database.
The goal is if the stored procedure completes successfully to add logic to reset a maintenance bit (Boolean 0 or 1) in a table in the same database, where 0 means no users are allowed on the database and 1 opens the database to users.
This is important because if there are user connections during the execution of the SP the maintenance will fail, so it is important for the "maintenance bit" as I call it,  to be switched OFF prior , and ON after the successful run of the maintenance SP.
I am trying to incorporate this within the TRY..CATCH block, not sure how.
Please see attached and come up with your suggestions ans examples as to how to accomplish this.

Thank you,
DB Dev ToolsMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment

8/22/2022 - Mon

As your code is now it always be set to 1.

The update to set it on 1 again must be in the try-part (and the catch also can fire on problems with the update) or at least a switch must be set there so  the update can stay where it is (and not possible fire the catch) but then surrounded with a test on that switch
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you Scott. This is doing what I expected it to do.
Your help has saved me hundreds of hours of internet surfing.