Elon's Blog

Stuff that may be of use to someone…

Archive for the ‘SQL Server’ Category

Microsoft SQL Server – General Product and News related Articles

MDX – A new fast approach to the Last Ever Non Empty value

Posted by Elon B. on March 27, 2011

Chris Webb has come up with a really fast way of performing this traditionally poor performaning requirement.
You can find a full explanation of this here

Advertisements

Posted in SQL Server, SSAS | Leave a Comment »

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.

Posted in SQL Server, SSRS, T-SQL | 2 Comments »

 
%d bloggers like this: