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...

SELECT
  
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]
  
CASE
      
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 jb.name 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 

2 comments:

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?