Thursday, June 22, 2017

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade.

The SSRS report was not able to access the SQL server that was being used in the stored procedure.

So I had to rerun the report subscription manually.

To re-run the SSRS report subsciption I followed two steps.

Step 1:

I have used the below query to identify the job id of the failed reports.

     S.ScheduleID AS SQLAgent_Job_Name, Sub.LastStatus, sub.LastRunTime, LastRunStatus
     ,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)
sub.LastRunTime > GETDATE() - 1 and  C.Name LIKE 'Reportname' 

Step 2:

I have copied the scheduleid and used a filter in the Jobs of the SSRS server as shown below.

Then Right click the Job and click 'Start Job at Step'

There are several other practices to achieve the same result.but I find that this is easier to follow.

No comments:

Power BI Did You Know -- Series 1 (Contact Us button)

Did you know that you can add a contact us button in your Power BI reports ? Here is how you add a contact us button in your power bi rep...