Manually Run Trigger Schdeuled Subscribed SSRS Report

Ever wondered how to test a Scheduled SSRS Report or how to Manually Run/Trigger a Scheduled SSRS Report for testing or for re-sending needless to wait till the next schedule of subscription? Yes, this is achievable and can be done.

There are two ways you can accomplish this.

Every schedule subscription has a SQL Server Agent Job associated with it, one way is to find out this Job and run it.

Second way is to find the subscription ID of the scheduled Report and Trigger it using AddEvent Stored Procedure.

Below is the script to accomplish this task.  All you need to do is replace the Reportname which I have used as TESTSCHEDULERUN with your reportname and execute the script using either of the step given below.

USE ReportServer
GO
--Declare Variables to Hold the Values
DECLARE
@myJobName NVARCHAR(250)
,@myEventData NVARCHAR(250)
--Get the Associated JobName and Subscription ID of the Scheduled Report
SELECT
@myJobName = SJ.NAME
,@myEventData = RS.SUBSCRIPTIONID
FROM REPORTSERVER.DBO.REPORTSCHEDULE RS JOIN MSDB.DBO.SYSJOBS SJ
        ON CAST(RS.SCHEDULEID AS NVARCHAR(MAX)) = SJ.NAME
        INNER JOIN REPORTSERVER.DBO.REPORTSCHEDULE RSC
        ON SJ.NAME = CAST(RS.SCHEDULEID AS NVARCHAR(MAX))
        INNER JOIN REPORTSERVER.DBO.SUBSCRIPTIONS S
        ON RSC.SUBSCRIPTIONID = S.SUBSCRIPTIONID
        INNER JOIN REPORTSERVER.DBO.[CATALOG] RSE
        ON S.REPORT_OID = RSE.ITEMID
WHERE RSE.NAME = 'TESTSCHEDULERUN'

--First Method to Invoke the Scheduled Report using AddEvent SP   
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@myEventData

--Second Method to Invoke the Job associated with the Scheduled Report using sp_start_job
EXEC msdb.dbo.sp_start_job @job_name = @myJobName

Sample Output

Now how to check the status of what you have run above.

Same Query with little tweak will give you the output.

SELECT
RSE.NAME
, S.DESCRIPTION
, LASTSTATUS
, EVENTTYPE
, LASTRUNTIME
, DATE_CREATED
, DATE_MODIFIED
FROM REPORTSERVER.DBO.REPORTSCHEDULE RS JOIN MSDB.DBO.SYSJOBS SJ
        ON CAST(RS.SCHEDULEID AS NVARCHAR(MAX)) = SJ.NAME
        INNER JOIN REPORTSERVER.DBO.REPORTSCHEDULE RSC
        ON SJ.NAME = CAST(RS.SCHEDULEID AS NVARCHAR(MAX))
        INNER JOIN REPORTSERVER.DBO.SUBSCRIPTIONS S
        ON RSC.SUBSCRIPTIONID = S.SUBSCRIPTIONID
        INNER JOIN REPORTSERVER.DBO.[CATALOG] RSE
        ON S.REPORT_OID = RSE.ITEMID
WHERE RSE.NAME = 'TESTSCHEDULERUN'