Scenario
Whilst at a client site, I was asked whether you could stop (disable) a users Report Subscription based upon an external event, such as another SQL Agent Job or Task outcome, such as failing.
The approach I took to demonstrate how you could achieve this was to create another SQL Agent Job which “enabled” the users Report Subscription Job, based upon the occurrence of a “flag” record in another table. This table could easily be created by the calling control job, or a prior job step.
Steps
Here we can see a Report Subscription that has been created by a user: (click on any image for full-size view)
Subscription Details:
The job is set to run at 19:12.
Using SSMS (SQL Server Management Studio) and connecting to the Database that the “ReportServer” database resides on, we can use the “Job Activity Monitor” to see the Job that was automatically created for this subcription.
Job Name: A48C0610-4A9F-4847-97E2-BAF7798DEE0C
We can write a SQL Script to view the Job Details:
USE msdb
SELECT *
FROM msdb.dbo.sysjobs
WHERE name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’
To view the Details of all the Jobs on the Server we can use the following script:
USE msdb
GO
SELECT Getdate() AS ‘ExecutionTime’
GO
SELECT @@SERVERNAME AS ‘SQLServerInstance’
GO
SELECT j.[name] AS ‘JobName’,
enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM msdb.dbo.sysjobs j
INNER JOIN MASTER.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO
Script to Enable/Disable Job
/* script to disable by job */
EXEC msdb.dbo.Sp_update_job
@job_name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’,
@enabled = 0; — 0 to disable 1 to enable
GO
Therefore, we could setup another SQL Job, which will run this script dependant upon some logical condition. i.e. where a certain value or number of records exists in a table, etc.
To enable/disable the job based upon say records, in a table, adapt the following script accordingly to your requirements. I have used a temporary table to demonstrate the principle.
/* script to enable(1) or disable(0) by job based upon records in table @flagtable */
USE msdb
DECLARE @flagtable TABLE (
flag INT)
DECLARE @DoIt INT
SET @DoIt = 0
-- let?s add a record to this table for test purposed. To Disable the Job we would comment out the following two rows
INSERT INTO @flagtable
(flag)
VALUES (1)
-- so lets say we will enable the job if a record exists in the @flagtable
SELECT @DoIt = COUNT(*)
FROM @flagtable
/* script to enable(1) or disable(0) by job based upon records in set @flagtable */
EXEC msdb.dbo.Sp_update_job
@job_name = 'A48C0610-4A9F-4847-97E2-BAF7798DEE0C',
@enabled = @DoIt;
GO
Script to DISABLE the SQL Agent Job as @DoIt = 0
Script to ENABLE the SQL Agent Job as @DoIt = 1
So, to automate this task we can set up a new SQL Agent Job to run this script as shown;
Create the following Step, and add the T-SQL to enable or disable the Job as required.
Hence, if more than 1 record exists in our “flag” table, it will ENABLE the Report subscription Job.
If no records exist in the “Flag” table, the Report Subscription Job will be set to Enabled = No (i.e. Disabled).
Remember, you will use your own logic to set the @DoIt parameter based upon your own requirements.
You can run this Job Manually or on a Scheduled as required:
Conclusion
Though I applied this to a Report Subscription Job, it could easily be adapted for other types of Jobs.
The beauty of it is that the original Job and its associated schedule timings remain unaltered. All we are doing is disabling/enabling the Job as necessary.
Elon's Blog 