How to get subscription associated with the reports on Report Server

SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,Schedulename=sch.Name
      ,ScheduleType = sch.EventType
      ,ScheduleFrequency =
       CASE sch.RecurrenceType
       WHEN 1 THEN ‘Once’
       WHEN 2 THEN ‘Hourly’
       WHEN 4 THEN ‘Daily/Weekly’
       WHEN 5 THEN ‘Monthly’
       END
       ,su.Parameters
  FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
  JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s