Scheduling Subscriptions with SSRS

I need to schedule Report subscriptions (email) every half hour for specific work hours of the day (ex:  6am through midnight).    In the world of Blackberry's, emails are instantly available AND the execs do not want to receive these subscriptions from midnight through 6am.  

SSRS does not appear to have a Start Time and End Time capability.  The brute force workaround is to create a subscription for each time period.  For a 6am through midnight work day that would be 36 subscriptions,  

Is there any work around (or SSRS feature not yet found) that will save us from creating multiple reports with up to 36 subscriptions each?
Who is Participating?
tpi007Connect With a Mentor Commented:
You could create shared RS scheule to run once a day, I generally use shared schedules where appropriate and attach reports to the schedule. Then create a SQL Agent job for to run RS  report SQL Agent job directly using standard SQL Agent job scheduling which will support your requirements. All scheduled reports are created as a SQL Agent job by Reporting Services with a GUID as job name.  

Identify SQL Agent job for shared schedule/report specific schedule, will be a GUID, for RS report/shared schedule you want to schedule differently. You can view the job schedule to help idientify correct RS report/schedule. This info can located in a table in the ReportServer database but I cannot recall exact tables.
1. Setup shared schedule to run once a day or whatever appropriate
2. Create SQL Agent job with specific schedule requirement
3. Idenitfy SQL Agent job (name will be GUID) as above
4. Add step(s) Transact SQL step to call RS Job to run against MSDB database
5. Add following SQL where the GUID will be the GUID of your report/shared schedule

exec sp_start_job '11C79303-B901-4483-A9B8-741302B83491'
fesnyngAuthor Commented:
>>can located in a table in the ReportServer database but I cannot recall exact tables.
see code snippet below.  Change the WHERE clause as needed to locate the exact report.  

SELECT sch.ScheduleId, sub.[Description] 
FROM dbo.Subscriptions sub
INNER JOIN dbo.Schedule sch ON sub.SubscriptionId = sch.[EventData]
WHERE sub.LastRunTime IS NULL

Open in new window

fesnyngAuthor Commented:
PERFECT.  Thanks for the step by step.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.