Thursday, 24 May 2007

Listing Report Server Subscription Details

Ever get annoyed by the list of SQL Agent Reporting Services jobs with their non-sensical names. You don't know which ones are active subscriptions and which ones are only once off? Users ring you to say they're not receiving the report they subscribed to, and there is no quick way to see how the subscription is setup.

In Report Manager you can only view the subscriptions for the selected report. To see the schedule details to need to drill down another couple of screens.

Using Management Studio you are still stuck with looking at each individual report and then each subscription. At least when you bring up the properties for the subscription it's all within one dialog.

The following query will list the basic details about all the subscriptions in the ReportServer database. In the next iteration I hope to include more details such as file format, parameters selected, etc...

cat.path AS [ReportServerPath]
own.username AS SubscriptionCreatedBy
jb.date_created AS [SubscriptionCreatedOn]
mod.username AS [SubscriptionModifiedBy]
sub.ModifiedDate AS [SubscriptionModifiedOn]
sub.Description AS [SubscriptionDescription]
sub.EventType AS [SubscriptionType]
WHEN PATINDEX('%Recurrence%'sub.MatchData) > 0THEN 'Recurring' 
ELSE 'Once Off'
END AS [ScheduleType]
sch.LastRunTime AS [SubscriptionLastRun]
FROM msdb.dbo.sysjobs jb
INNER JOIN [ReportServer].[dbo].[Schedule] sch
ON CAST(sch.scheduleid AS VARCHAR(40))
INNER JOIN [ReportServer].[dbo].[Subscriptions] sub
ON sch.eventdata sub.subscriptionid
INNER JOIN [ReportServer].[dbo].[Catalog] cat
ON sub.Report_OID cat.ItemID
INNER JOIN [ReportServer].[dbo].[Users] own
ON sub.OwnerID own.UserID
INNER JOIN [ReportServer].[dbo].[Users] mod
ON sub.ModifiedByID mod.UserID 


Oliver said...

Great query. Very useful. Thanks.

Anonymous said...

Great query, but how do I tell when the next time it is scheduled to run?