Posted on Leave a comment

Found this T-Sql post useful

Execute SSRS Report Subscriptions Manually
Posted on September 5, 2012 by sqlgirl
Execute SSRS Report Subscriptions Manually. The following code will allow the report developer to manually execute a report subscription if the subscription has failed (or is needed to be run ad-hoc).

1. Open SQL Server Management Studio
2. Connect to the Report Server.
3. Choose ReportServer for the database.
4. Execute the following Query to determine the SQLAgent Job Name (Job ID).

/*Execute SSRS Subscription Manually*/
/*Connect to Database ReportServer*/
SELECT
S.ScheduleID AS SQLAgent_Job_Name
,SUB.Description AS Sub_Desc
,SUB.DeliveryExtension AS Sub_Del_Extension
,C.Name AS ReportName
,C.Path AS ReportPath
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
C.Name LIKE ” –Enter Report Name to find Job_Name

5. Connect to MSDB Database on the Report Server.
6. Insert the SQLAgent_Job_Name in the following and execute.

/*Connect to Database MSDB on the Reporting Server*/
/*Enter SQLAgent_Job_Name to execute the subscription based on Job ID*/
USE msdb
EXEC sp_start_job @job_name = ” –Enter SQLAgent_Job_Name

I used the exec msdb.dbo.sp_start_job ….. since I was calling it from within a sp.