I Didn’t Receive My Subscription

Ever have a Reporting Services (SSRS) subscription that didn’t fire off as you thought it should?  Or was SQL Agent offline when subscriptions should have been fired?  Or did you just create a subscription and want to find out if it’s been configured properly?

Fortunately all of the information is sitting neatly and plainly in your SQL Server database.  Unfortunately it the plainly part isn’t quite the truth.  If you’ve looked before, subscriptions are named with uniqueidentifiers.  These values don’t exactly read in a manner that help identify which subscription they correspond to.

The query below provides a list of all current subscriptions in SSRS and also the last execution date.  The final column has the SQL syntax that would be needed to start the job to execute the subscription.  A little modification and the query can also be used to find execution history on the subscription.

;WITH cte (job_id, job_name, execution_time, execution_order)
AS
(
SELECT DISTINCT j.job_id
    ,j.name
    ,CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/')
        + SPACE(1)
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':'))
    ,ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/')
        + SPACE(1)
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':')) DESC)
FROM msdb.dbo.sysjobs j (nolock)
    INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobhistory jh (nolock) ON j.job_id = jh.job_id
WHERE c.name ='Report Server'
)
SELECT
    x.job_name
    ,c.name
    ,x.execution_time
    ,c.path
    ,su.description
    ,CONVERT(varchar(max), su.ExtensionSettings) as ExtensionSettings
    ,'EXEC msdb..sp_start_job ''' + x.job_name + '''' as SQLStatement
FROM cte x
    INNER JOIN dbo.Schedule sc ON x.job_name = CONVERT(varchar(100), sc.ScheduleID)
    INNER JOIN dbo.ReportSchedule rs ON sc.ScheduleID = rs.ScheduleID
    INNER JOIN dbo.Subscriptions su ON rs.SubscriptionID = su.SubscriptionID
    INNER JOIN dbo.Catalog c ON su.Report_OID = c.ItemID
WHERE execution_order = 1
ORDER BY 3, 2