Conditional TRY..CATCH block

RICuser
RICuser used Ask the Experts™
on
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,
RICUser
Maintenance-Job-Step.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
--Close the database for all user activity to allow maintenance to be run
UPDATE on_off_table

SET open_closed_table  = 0

BEGIN TRY

      EXEC maintenance_SP --This is the maintennace SP that runs every night.

     UPDATE on_off_table

     --proc was successful, so let users back into this db
     SET open_closed_table  = 1

END TRY

BEGIN CATCH

         INSERT INTO [dbo].[maintenance_log_table]--THIS is a table that collects the error of the SP if any

         SELECT

         @database_name AS DatabaseName,

         'maintenance_SP' AS ProcedureName,

         ERROR_NUMBER() AS ErrorNumber,

         ERROR_SEVERITY() AS ErrorSeverity,

         ERROR_MESSAGE() AS ErrorMessage,

         @timestamp AS Date_Time

    --proc was NOT successful, so users are not allowed back into this db

END CATCH;

Author

Commented:
Thank you Scott. This is doing what I expected it to do.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial