Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS - Run multiple Subscriptions one after another once each has completed

Answered SSRS - Run multiple Subscriptions one after another once each has completed

  • Sunday, January 20, 2013 10:45 AM
     
     
    Sign In to Vote
    0

    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.

    Many Thanks

     

All Replies

  • Sunday, January 20, 2013 2:53 PM
     
     Answered
    Sign In to Vote
    0

    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 //
    http://brentgreenwood.blogspot.com




     
  • Friday, January 25, 2013 12:04 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Brent,

    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: 

    DECLARE @SubscriptionStatus AS nvarchar(260)


EXEC msdb.dbo.sp_start_job '4B7FA89E-0B56-4ED1-9A0F-37E5D03318CB' /*First long running    report*/
WAITFOR DELAY '00:00:30'

SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where subscriptionid =    'E156FD91-E7F9-43EC-8B73-28622834EACB')


CheckSubscription1:
    IF @SubscriptionStatus = 'Pending'
    BEGIN
        PRINT 'The First long running Subscription is still running'
        WAITFOR DELAY '00:01:00'
        SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where     subscriptionid = 'E156FD91-E7F9-43EC-8B73-28622834EACB')
    END

IF @SubscriptionStatus = 'Pending'
    GOTO CheckSubscription1

ELSE

EXEC msdb.dbo.sp_start_job '6D3300BC-ACA9-4EEE-A5F9-546635B585E0' /*Second long running    report*/
WAITFOR DELAY '00:00:30'

SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where subscriptionid =        'AD54215A-5B7F-48B2-81B2-52C299875AD6')

CheckSubscription2:
    IF @SubscriptionStatus = 'Pending'
    BEGIN
        PRINT 'The second long running Subscription is still running'
        WAITFOR DELAY '00:01:00'
        SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where     subscriptionid = 'AD54215A-5B7F-48B2-81B2-52C299875AD6')
    END

IF @SubscriptionStatus = 'Pending'
    GOTO CheckSubscription2

ELSE

EXEC msdb.dbo.sp_start_job '84FD876A-1945-405E-A344-6279E27DCD68' /*Third long running     report*/

WAITFOR DELAY '00:00:30'

SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where subscriptionid =    '8F446935-5450-458F-9076-7AD9FC78D456')

CheckSubscription3:
    IF @SubscriptionStatus = 'Pending'
    BEGIN
        PRINT 'The third long running Subscription is still running'
        WAITFOR DELAY '00:01:00'
        SET @SubscriptionStatus = (SELECT LastStatus FROM Subscriptions Where    subscriptionid = '8F446935-5450-458F-9076-7AD9FC78D456')
    END

IF @SubscriptionStatus = 'Pending'
    GOTO CheckSubscription3

ELSE

PRINT 'All long running report run'

    Thanks