查询从SSRS中的ExtensionSettings中提取所有路径

Question

我需要提取SSRS中订阅表的ExtensionSettings字段中的路径列表。有人有这个的剧本吗?

Answer 1

我找到了一个很好的脚本:http://www.andrewing.co.uk/parse-ssrs-subscriptions-table/

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