December 15, 2010 at 11:50 am
Is there a way to prevent a subscription or snapshot from running if the report will produce no data? Thank you.
January 3, 2011 at 11:02 am
Yes, but it is a fairly indirect process.
First I would suggest that you allow reports to run even if there is no data. That way people know there is no data rather than wondering of something broke. There is a property in SSRS tables, etc. that is called NoRowsMessage that will allow you to put a message in the report that says there is no data being returned.
If you still want to disable the report you have a bit of digging to do. All Report Manager subscriptions are turned into SQL Agent jobs on the server. So you have to figure out what Agent job is the report in question. This code should lead you in the right direction:
SELECT cat.[Name]
,cat.[Path]
,res.ScheduleID AS JobID
,sub.LastRuntime
,sub.LastStatus
,sch.next_run_date
,sch.next_run_time
,LEFT(CAST(sch.next_run_date AS CHAR(8)),4)
+ '-' + SUBSTRING(CAST(sch.next_run_date AS CHAR(8)),5,2)
+ '-' + RIGHT(CAST(sch.next_run_date AS CHAR(8)),2)
+ ' ' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)),1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)),2) END
+':' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),2,2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),3,2) END
+ ':00.000' AS NextRunTime
,CASE WHEN job.[enabled] = 1 THEN 'Enabled' ELSE 'Disabled' END AS JobStatus
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
ORDER BY cat.[Name]
GO
Then once you know the report and job you want to disable. You need to build a job that will evaluate if the report is going to return any data and then use the results of that query to enable or disable you job. This script will disable your job.
UPDATE msdb.dbo.sysjobs
SET enabled = 0
FROM msdb.dbo.sysjobs
WHERE [name] IN ('Put your JobID numbers here')
If you want to enable the job you can use
--Enable your queries again.
UPDATE msdb.dbo.sysjobs
SET enabled = 1
FROM msdb.dbo.sysjobs
WHERE [name] IN ('Put your JobID numbers here')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply