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.