Summary
This note describes how to manually run the SSRS report subscription. This can be applied when
- Test if the subscription work when setting up the subscription
- Manually run subscriptions in case of failure.
Details
Running a report subscription is an "Event" in the SSRS server. Therefore, manually running a subscription can be as simple as adding an event into the SSRS database and this can be done via the script below on your SSRS database.
EXEC AddEvent @EventType='TimedSubscription', @EventData='[YourReportSubscriptionID]'
Note: the SSRS database name usually in the format of 'ReportServer' then '$' and then the instance name
To automate the script further for re-runing failed subscriptions, you can fetch the subscriptionID that has a failed last run status (laststatus = 'Thread was being aborted.') from the Subscriptions table, then build the addEvent script onfly. Below is a sample of completed script.
DECLARE @strSQL AS NVARCHAR(MAX)
SET @strSQL = ''
SELECT @strSQL = 'EXEC AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(SUBSCRIPTIONID AS NVARCHAR(MAX)) + ''' ' + CHAR(13)+CHAR(10) + 'GO' + + CHAR(13)+CHAR(10) + @strSQL
FROM SUBSCRIPTIONS
WHERE laststatus = 'Thread was being aborted.'
EXEC @strSQL
To use the script above, please follow the steps
1. Connect to your SSRS database server via SSMS
2. Connect to the SSRS database (refer to the note above for database name)
3. Click on New Query button on the upper-left screen
4. Copy and paste the script above
5. Click on Execute button to run the script
6. The result should be "1 row effected"
7. It will take about 30 seconds (also depends on how long the report takes to run), then the status should be change on the SSRS subscription screen
The script can also be modified to run the subscription manually for a specific report. see script below. NOTE, if the report has more than one subscriptions, all subscriptions will be ran
DECLARE @strSQL AS NVARCHAR(MAX)
SET @strSQL = ''
SELECT @strSQL = 'EXEC AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(SUB.SUBSCRIPTIONID AS NVARCHAR(MAX)) + ''' ' + CHAR(13)+CHAR(10) + 'GO' + + CHAR(13)+CHAR(10) + @strSQL
FROM CATALOG RPT
INNER JOIN SUBSCRIPTIONS SUB ON RPT.ITEMID = SUB.REPORT_OID
WHERE NAME = '[YourReportName]'
PRINT @strSQL