reporting-servicesssrs-2008subscriptions

SSRS Subscriptions - How to view ALL report recipients


I've written an SSRS report to help me keep track of SSRS subscriptions. I've repurposed a script that will use Reportserver.dbo.Subscriptions.LastStatus to view email recipients, however, it will only list the first 520 characters of LastStatus. Because some of our distribution lists are quite large, some of the names that my script searches for are not being found (even though they are part of the distribution). Below is the script that I am using:

SELECT Reportname = c.Name 
  ,FileLocation = c.Path
  ,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.dbo.Subscriptions su 
  JOIN Reportserver.dbo.Catalog c 
    ON su.Report_OID = c.ItemID 
  JOIN Reportserver.dbo.ReportSchedule rsc 
    ON rsc.ReportID = c.ItemID 
   AND rsc.SubscriptionID = su.SubscriptionID 
  JOIN Reportserver.dbo.Schedule Sch 
    ON rsc.ScheduleID = sch.ScheduleID 
WHERE  LastStatus like @Email
ORDER BY LastRunTime DESC

Any code that I have found online uses the LastStatus column to display this data. If anyone has any suggestions as to a more complete way for me to list all of the members of the report distribution list, I would appreciate it.


Solution

  • Below is SQL to query for the full text of the subscription parameters. I think this will work with extremely long address lists, but I don't have a test server with long address lists available right now.

    If using this in production, I'd probably throw in a couple of WITH ( NOLOCK )'s and wouldn't expect support from MS on problems.

       ;
       WITH subscriptionXmL
              AS (
                   SELECT
                    SubscriptionID ,
                    OwnerID ,
                    Report_OID ,
                    Locale ,
                    InactiveFlags ,
                    ExtensionSettings ,
                    CONVERT(XML, ExtensionSettings) AS ExtensionSettingsXML ,
                    ModifiedByID ,
                    ModifiedDate ,
                    Description ,
                    LastStatus ,
                    EventType ,
                    MatchData ,
                    LastRunTime ,
                    Parameters ,
                    DeliveryExtension ,
                    Version
                   FROM
                    ReportServer.dbo.Subscriptions
                 ),
                     -- Get the settings as pairs
            SettingsCTE
              AS (
                   SELECT
                    SubscriptionID ,
                    ExtensionSettings ,
        -- include other fields if you need them.
                    ISNULL(Settings.value('(./*:Name/text())[1]', 'nvarchar(1024)'),
                           'Value') AS SettingName ,
                    Settings.value('(./*:Value/text())[1]', 'nvarchar(max)') AS SettingValue
                   FROM
                    subscriptionXmL
                    CROSS APPLY subscriptionXmL.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries ( Settings )
                 )
        SELECT
            *
        FROM
            SettingsCTE
        WHERE
            settingName IN ( 'TO', 'CC', 'BCC' )