Run Subscription from T-SQL

As a multi client facing company, we scheduled quite a lot reporting service subscriptions at the backend. Some of them are data-driven subscriptions, some of them are time-driven subscriptions, and some are running from SSIS, a custom control flow task to generate batch reports by calling ReportExecution2005 web service, for example, customer fulfilment form.

Occasionally we will receive the request to re-run the subscription. Instead of navigating to reporting service site and change the subscription setting manually, below script will shot start subscription immediately:

 use ReportServer  
 go  
 DECLARE @JobID varchar(50)  
  SELECT @JobID = S.ScheduleID   
  FROM ReportSchedule RS JOIN Schedule S ON RS.ScheduleID = S.ScheduleID  
    JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID  
    JOIN [Catalog] C ON RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID  
  WHERE C.Name LIKE '%agl%' --subscription report name comes here  
  EXEC MSDB..sp_start_job @job_name = @JobID