Parse SSRS Subscriptions Table
Information about Report subscriptions is held in ReportServer.dbo.Subscriptions table.
ExtensionSettings
XML Fragment with root node <ParameterValues> Contains information about the format and destination of the report.
Each <ParameterValue> element has child <Name> and <Value> nodes : named according to classical EAV theory.
- Windows share parameters
- PATH : windows share
- FILENAME : filename (excludingExtension)
- FILEEXTN : True or False
- USERNAME , PASSWORD encrypted values
- RENDER_FORMAT : Excel, etc
- WRITEMODE : Overwrite…
- Email parameters
- TO | CC | BCC | ReplyTo : email addresses
- IncludeReport : True or False
- RenderFormat : HTML, Excel, etc
- Subject : Subject of the email
- Comment : text included in the email body
Fragment of SQL to parse useful values.
WITH S AS ( SELECT TOP 1000 [SubscriptionID], ExtensionSettings = cast(ExtensionSettings as xml) FROM [ReportServer].[dbo].[Subscriptions] ) SELECT S.SubscriptionID, TransmissionMethod = CASE ExtensionSettings.value('(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)') WHEN 'TO' THEN 'Email' WHEN 'PATH' THEN 'File Share' ELSE 'Unknown' END, FileSharePath = ExtensionSettings.query(' for $parameter in /ParameterValues/ParameterValue let $name := $parameter/Name let $value := $parameter/Value where $name = "PATH" return data($value)'), FileName = ExtensionSettings.query(' for $parameter in /ParameterValues/ParameterValue let $name := $parameter/Name let $value := $parameter/Value where $name = "FILENAME" return data($value)'), RenderFormat = ExtensionSettings.query(' for $parameter in /ParameterValues/ParameterValue let $name := $parameter/Name let $value := $parameter/Value where $name = "RENDER_FORMAT" or $name = "RenderFormat" return data($value)'), EmailSubject = ExtensionSettings.query(' for $parameter in /ParameterValues/ParameterValue let $name := $parameter/Name let $value := $parameter/Value where $name = "Subject" return data($value)') ,ExtensionSettings FROM S
References
- https://www.simple-talk.com/sql/database-administration/ad-hoc-xml-file-querying/