Altering SQL Agent jobs during job runtime can give you some options as to how you can force other jobs
to react based on events. There are a listing of system procedures in the MSDB database available for
you to use so you can literally alter your entire timelines and schedules based on a failure, success or
even OS or network related issues. Here is a basic example of disabling and enabling a job
based on a monitoring task.

In this example there is a requirement to monitor a table that is being used to monitor finish good allocation
to a manufacturing facility to prevent volume from going over capacity.

Job 1 performs a monitoring task every 30 minutes. Basically querying the table that holds the count of the volume
the facility has received that day (starting at midnight). At midnight the volume is replenished by means of the value
specified in the same table. There is a need to monitor the capacity and once under 2000 units are remaining,
you are required to send notifications out to management. Notifications should go out once.
The key is the one notification. This is one option of getting around that using the system procedures to alter job properties.

Step 1) create the table to monitor

Insert the data in order to test out monitor

Step 2) create the job to monitor the volume count.

So far we have successfully created a table to hold our capacity values that we negate based on units
sent to eh facility and we have a job to monitor the capacity. Problem is the job will send out an
email to management every 30 minutes which is not something managers like much. In order to keep the
DBA from manually going in after the notification is sent we need to disable the job. We can
use sp_udpate_job to get that task done.

First query the sysjob table in MSDB to grab the job_id. You can also use the name of the job but I
think that is prone to typos and copying the job_id directly out of sysjob is a bit more stable.

This shows "8386ED61-E2A8-4DE5-B54C-4A7DFD3CDDEC" for the Capacity Restriction Monitor Plant 01 job_id

Syntax for sp_update_job is as follows. One key note is you can use job_name or job_id to update the job with
this system procedure BUT you cannot specify both. Other questions I hear are does the SP update other properties
when you do not specify them. The answer is no, the SP will only update what parameter you specify.

syntax

So to form this call and disable the job we need to add to the job this statement.

After the db mail send put issue a Go and then add

Now when the job runs it will send the notifications out and disable the job so no further
notifications will be sent.

Every night at midnight we stated the volume should be replenished to the maximum allowed units. This is
simply another job to reset the Vol_Status in our table to the value in Volume. The added requirement we need to
add to that job is re-enabling out monitor. If we don't then the call to change the enable value in sysjob will remain
0 and the we break out monitoring process. So create another job scheduled for midnight with the statement of

Now the process has gone through a complete cycle and the monitor and volume count starts clean.