SSRS Subscriptions

Overview

SSRS Subscriptions is the part of a report server that runs when nobody is watching, on one page. The portal lists subscriptions one report at a time, which is fine when you know which report to look at and no help when the question is “what does this server send out at six in the morning, and why did the finance one stop”.

Four things are side by side here that the portal does not show together:

  • Whether it is switched on. There is no enabled column on a subscription. What decides whether one fires is a SQL Server Agent job named for its schedule, so the State column comes from msdb rather than from the catalog.
  • Whether the report server switched it off itself. The report server sets InactiveFlags when the delivery extension is gone, the report was deleted or the credentials will no longer do. Those subscriptions look normal in a list that does not read that column.
  • Whether it can run unattended at all. A report whose data source prompts for credentials, or uses integrated security, has nothing to supply them with at two in the morning.
  • Whether its schedule is shared. One Agent job serves one schedule, so switching a shared schedule reaches subscriptions you did not select.

Enable and disable run from this page. Delete does not: it produces the script and hands it over.

Where to find it

Only shown for a database holding a Reporting Services catalog.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.Subscriptions, dbo.Catalog, dbo.Users, dbo.ReportSchedule, dbo.Schedule and dbo.DataSource.
  • SELECT on msdb.dbo.sysjobs_view and msdb.dbo.syscategories for the State column. On a default msdb both are granted to SQLAgentUserRole, but sysjobs_view shows a login only the jobs it owns unless it is sysadmin or a member of SQLAgentReaderRole (which SQLAgentOperatorRole and RSExecRole include). The page checks the permission before reading, so a login without it still gets the page, with State reading Unknown.
  • Enable and Disable run msdb.dbo.sp_update_job, which needs membership of SQLAgentUserRole in msdb and ownership of the job, or membership of sysadmin.

The two views

The toolbar switches between Next run and Last run.

Next run

Subscriptions in the order they will next fire, soonest first. The value on the right reads in 20 minutes, in 5 hours, due now, disabled, not scheduled, or on demand for a subscription with no schedule. Subscriptions with no next run time sort to the far end and draw no bar, so the bars that are drawn keep a readable scale.

Last run

Subscriptions by how long it has been since they last ran, longest first, so one that has never run sorts to the top, reads never and draws no bar.

Reading the bars

Each bar is labeled with the report name, with the subscription’s description underneath, or its delivery method when it has no description.

Mark Meaning
Green bar Last status reads as a success
Amber bar The Agent job is disabled, or the subscription has never run
Red bar Marked inactive by the report server, or the last status reads as a failure
Amber outline Inactive, Agent job disabled, or a data source that needs interactive credentials (not for a data driven subscription)

Hover over a bar for the report path, purpose and delivery method, state and schedule, the last run and last status, the owner and the notes.

Reading the grid

Column What it holds
Report The report’s catalog path, or (report deleted)
State Enabled or Disabled from the Agent job, Unknown when no job was visible, or No schedule
Notes What the page derived about the row
Last status The report server’s own last status text
Purpose Scheduled delivery, Data driven delivery, Cache refresh or Snapshot refresh
Delivery Email, File share, Cache refresh, or the delivery extension’s own name
Schedule The recurrence in words, such as Weekly on weekdays, with (shared: name) for a shared schedule
Next run How soon it next fires, as on the chart
Last run When it last ran, or never
Owner The subscription owner
Description The subscription’s description

Layout. The columns that say whether a subscription is healthy (State, Notes and Last status) come straight after Report, so they are on screen at 1280 x 900 without scrolling sideways. Every text column is capped, Description takes all the width that is left (never less than 150 px), and a value that is still cut off shows in full in the tooltip when the mouse rests on its row.

State and Notes are colored when the subscription is not in a healthy state. The Notes column says, where it applies:

  • why the report server marked it inactive, such as the report was deleted or the credentials will not do
  • that the report it runs is no longer in the catalog
  • that a data source under the report prompts for credentials or uses integrated security (not said for a data driven subscription)
  • that its Agent job is disabled, so it will not fire
  • that no Agent job for its schedule was visible to this login
  • how many other subscriptions, and how many report level schedules such as a cache expiry or a snapshot, share its schedule
  • that it has never run

Findings

The headline counts the subscriptions and the reports they are on, how many are disabled and how many are failing, or says none of them are failing. The line under it adds, where they apply:

  • How many subscriptions the report server has marked inactive itself.
  • How many run against a data source that nothing can supply credentials for unattended.
  • How many have never run at all.
  • How many schedules are shared, so switching one reaches every subscription on it.
  • That this login has no SELECT on msdb.dbo.sysjobs_view, so State reads Unknown throughout. Otherwise, when the login could read Agent’s jobs and found none in the Report Server category, that the state column cannot say whether these fire: either the server has no scheduled work, or the account is outside SQLAgentReaderRole and sees only its own jobs.

Actions

The grid allows more than one row to be selected, and the menu acts on the whole selection.

  • Click a bar to select that subscription’s row in the grid.
  • Double click a bar to select its row and move the focus to the grid.
  • Right click a bar for the same menu as right clicking its row in the grid.
  • Right click one or more rows for:
    • Disable subscription… or Enable subscription…, shown only for what the selection can actually do. When nothing selected has an Agent job to switch, a grayed out Nothing here has an Agent job to switch is shown instead.
    • Copy the script that deletes subscription and Copy the script that disables subscription, followed by Open the delete script in SSMS and Open the disable script in SSMS when SSMS is installed.
    • Copy report path, Copy subscription id and Copy the Agent job name, for the first row selected.
    • Open Delivery History, Open Delivery Queue and Open Schedules. Each opens that page for every subscription on the server; none of them filters to the rows selected.
  • Right click an empty part of the chart to copy the chart to the clipboard.
  • The toolbar buttons Delivery history, Queue and Schedules open those pages.

With several rows selected the menu items name the count, such as Disable 4 selected subscriptions….

Enable and disable

Both switch the SQL Server Agent job behind each subscription’s schedule, with msdb.dbo.sp_update_job. Nothing in the report server catalog changes, so running the opposite action puts it back exactly as it was.

Each selected subscription is read again before the question is asked, so the confirmation describes what is true at the moment of the click. It shows:

  • what enabling or disabling does. Enabling fires from the next scheduled time and catches nothing up; disabling leaves a delivery already in the queue to go out.
  • a warning when a selected subscription’s schedule also carries subscriptions you did not select, or report level schedules such as a cache expiry or a snapshot, naming how many it reaches. Subscriptions that are in the selection are not counted
  • which selected subscriptions were skipped, because they are gone from the catalog or have no Agent job this login can see
  • the statements that will run

When nothing in the selection needs changing, an information dialog says there is nothing to enable or disable, and why.

Press Enable them or Disable them to go ahead, or Leave them alone. A run that works on everything closes silently and the page reloads. If any subscription did not change, a dialog lists each one with the server’s own error and advice for the common refusals.

Delete, as a script

Delete does not run from this page. Copy the script that deletes writes a commented script that calls DeleteSubscription once for each selected subscription. That one call is the whole removal, because the catalog’s own triggers do the rest when the Subscriptions row goes:

  • Subscription_delete_Schedule removes the subscription’s ReportSchedule link.
  • ReportSchedule_Schedule then deletes the Schedule row if nothing else uses it and it is not a shared schedule.
  • Schedule_DeleteAgentJob runs msdb.dbo.sp_delete_job for the Agent job named after that schedule.

Delivery history, recipient results and queued notifications for the subscription go with it through cascading foreign keys.

Under each call the script says what happens to the schedule. A shared schedule is left in place with its Agent job, with a note of what still runs on it. For a schedule that belonged to that subscription alone, the script adds a guarded sp_delete_job that runs only if the job is still there after its Schedule row has gone, so on a normal run it does nothing. The login running the script needs rights to delete those Agent jobs. Read it and take a backup of the catalog database before running it.

Where the data comes from

dbo.Subscriptions joined to dbo.Catalog for the report, dbo.Users for the owner, dbo.ReportSchedule and dbo.Schedule for the schedule. A second read lists the jobs in the Report Server category from msdb.dbo.sysjobs_view, matched on this side by job name, which is the schedule id. That read runs only when HAS_PERMS_BY_NAME says the login may, so a refusal leaves State at Unknown rather than failing the page.

Two details worth knowing:

  • The credential check follows the link. A report’s data source row that points at a shared data source takes its credential mode from the shared one, because that is the mode that decides whether the subscription can run.
  • A subscription is treated as data driven when its DataSettings column is set.

Messages you may see

This report server sends nothing out on its own. There are no subscriptions in the catalog. That is normal for a report server people only visit.

The report server query did not finish in time. Press F5 to try again.

Frequently asked questions

Why does State say Unknown? No Agent job named for the subscription’s schedule was visible to the login Database Health Monitor connected with. Either the report server never created one, or this login cannot see it: without SQLAgentReaderRole (or sysadmin) a login sees only the Agent jobs it owns, and without SQLAgentUserRole it cannot read the job list at all. The line under the headline says which, when no Report Server jobs were visible.

A subscription looks normal in the portal but has not sent anything. Where do I look? Read the Notes column. A subscription marked inactive by the report server, one whose Agent job is disabled, and one on a report whose data source prompts for credentials all look ordinary in the portal and all stop delivering.

Why is delete a script and not a button? Removing a subscription is permanent and takes its delivery history with it, and through the catalog’s triggers it can also remove a schedule and an Agent job in msdb. The page writes the script, with what the triggers will do spelled out above it, so you can read it, keep it in a change record and run it yourself.