This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Tuesday, October 16, 2012

Get Agent Job Name for SSRS Report Subscription

Every time when we create Reporting Service Report subscription , a new Agent Job creates.
Here is the useful script to get Agent Job Name for each subscription :

Use ReportServer
Schedule.ScheduleID AS SQLAgent_Job_Name,
Subscriptions.Description AS SubscriptionName,
Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath
FROM         ReportSchedule
INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

Or More Advance ...

 Schedule.ScheduleID AS AgentJobName,
 Subscriptions.Description AS SubscriptionName,
  Subscriptions.DeliveryExtension AS DeliveryExt,
  [Catalog].Name AS ReportName,
  [Catalog].Path AS ReportPath,
 SUBSTRING(ExtensionSettings, LEN('TO') + CHARINDEX('TO', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('TO', ExtensionSettings) + 1) - (LEN('TO') + CHARINDEX('TO', ExtensionSettings))) AS 'To Email recipient List',
CASE CHARINDEX('CC', ExtensionSettings) WHEN 0 THEN
   SUBSTRING(ExtensionSettings, LEN('CC') + CHARINDEX('CC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('CC', ExtensionSettings) + 1) - (LEN('CC') + CHARINDEX('CC', ExtensionSettings)))
END AS 'CC Email recipient List',
CASE CHARINDEX('BCC', ExtensionSettings) WHEN 0 THEN
 SUBSTRING(ExtensionSettings, LEN('BCC') + CHARINDEX('BCC', ExtensionSettings), CHARINDEX('', ExtensionSettings, CHARINDEX('BCC', ExtensionSettings) + 1) - (LEN('BCC') + CHARINDEX('BCC', ExtensionSettings)))

END AS 'BCC Email recipient List'

INNER JOIN Schedule  ON   ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions  ON   ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]  ON   ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID
  Subscriptions.DeliveryExtension = 'Report Server Email'