Sunday, January 20, 2013 10:45 AM
I’m hoping someone can help. I’m trying to programmatically run some SSRS subscriptions one after the other. The reports are all long running and consistently fail if triggered at the same time. At the moment we have about four different subscriptions spread out through the day ensuring that they don’t clash. Unfortunately this can waste quite a lot of time. The solution I have for this is to create a subscription that is not scheduled to run on all each of the reports in question and then get one job to trigger each subscription one after the other once each has finished running:
1) One job triggers the first subscription 2) Using a WAITFOR command give a few seconds for the subscription to run. 3) Using WAITFOR command check periodically that the subscription is running (‘Pending’) 4) When the WAITFOR check finds that the report has been sent the job triggers the next subscription and so on....
I know the code to trigger the subscription:
exec [ReportServerWSS].dbo.AddEvent @EventType='SharedSchedule', @EventData='011e83ff-344a-416a-83cb-1a9281e4205b'
I just need to know how to use the WIAITFOR whilst doing a check and then respond to the results of the check.
Sunday, January 20, 2013 2:53 PM
Here's a link to a post on an undocumented system proc you can hit to see what jobs are currently processing. Or just wait for the history to be updated in these sys views to know when a predecessor job is complete. Another route is to log to a control table and update the status of the job when complete, so that another process can poll that contol table to know when to fire.
But instead of building a custom sequencing process, you might consider first trying to set MaxQueueThreads to 1 to force the subscriptions to run serially. Simpler approach worth testing. Check out this forum post with additional links from Robert Bruckner that gives more details. I've implemented this on my main prod RS instance and have been able to avoid the occasional failures I was having when large subscriptions overlapped.
Hope that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
Friday, January 25, 2013 12:04 PM
Thanks for your response. The problem with your suggestion is that the processing of the subscription is not indicated by the job. The good news is that I figured out a solution: