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.