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/

no comment

Sorry, comments closed.