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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.