Elon's Blog

Stuff that may be of use to someone…

Enable or Disable a SSRS Report Subscription based upon another task or event.

Posted by Elon B. on July 31, 2010

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.

Advertisements

2 Responses to “Enable or Disable a SSRS Report Subscription based upon another task or event.”

  1. […] Enable or Disable a SSRS Report Subscription based upon … – Jul 31, 2010 · 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 …… […]

    Like

  2. […] Enable or Disable a SSRS Report Subscription based upon … – Jul 31, 2010 · 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 …… […]

    Like

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: