Overview: Quick tip about using T-SQL in an Agent Job to refresh a SQL Server Reporting Services snapshot report, rather than a schedule.

SSRS has the capability to schedule the refresh of report snapshots via a report-specific schedule or a shared schedule.  However, what if you don’t have a specific time you want the refresh to run?  Alternatively, what if you want the snapshot to be refreshed after an event occurs, such as ETL completion?

The first step is finding the ReportID (aka ItemID) assigned to the report by ReportServer.  The following query will return several pieces of information, including ReportID (aka ItemID):

SELECT
  NameOfReport = Cat.Name
  ,Cat.Path
  ,ReportID = Cat.ItemID
  ,NameOfAgentJob = Sched.ScheduleID
  ,LastExecutionTime = Cat.ExecutionTime

FROM ReportServer.dbo.Catalog Cat WITH(NOLOCK)

LEFT JOIN ReportServer.dbo.ReportSchedule Sched WITH(NOLOCK)
  ON Cat.ItemID = Sched.ReportID

WHERE Cat.Name = '<InsertReportNameHere>'

   

Now that you have the ID for the specific snapshot report you need to get refreshed, insert that ID for the EventData parameter in the following T-SQL statement:

exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='<InsertReportIDHere>'

Using this technique, you could add one or more T-SQL step(s) to kick off the refresh of subscriptions after ETL completes (or whatever other event you wish to trigger the refresh). 

   

If you are controlling refreshes via an Agent job step, you probably also want to make sure the report-specific schedule is set to “Once” or turn it off completely.  If you leave the schedule active (such as the “Once” option shown below), you will still have an Agent Job present that is associated to this schedule (same as how subscriptions are handled).  The name of this Agent job can be found by referring to the Sched.ScheduleID field in the query above.