Reports documentation report (metadata) revisited 2008
The script below is working fine. However, I would like to show in addition:
- dataset name
- the full SQL command text since only 50 characters are shown.
Can anybody help to overcome my bad xml knowledge ?
Best regards Joachim
WITH
XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
name
,
x
.value('CommandType[1]',
'VARCHAR(50)')
AS CommandType,
x
.value('CommandText[1]','VARCHAR(50)')
AS CommandText,
x
.value('DataSourceName[1]','VARCHAR(50)')
AS DataSource
FROM
(
select
name,
CAST
(CAST(content
AS
VARBINARY(MAX))
AS
XML)
AS reportXML
from
ReportServer.dbo.Catalog
where
content is
not
null
and
type
!= 3
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query')
r(x
)
WHERE
x.value('CommandType[1]',
'VARCHAR(50)')
is
null
--= 'CommandText'
ORDER
BY name
May 23rd, 2011 7:49am
Try this:
WITH
XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
name
,
x.value('CommandType[1]', 'VARCHAR(8000)') AS CommandType,
x.value('CommandText[1]','VARCHAR(8000)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(8000)') AS DataSource,
x1.value('@Name','VARCHAR(8000)') AS DataSetName
FROM
(
SELECT
name,
CAST
(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM
ReportServer.dbo.Catalog
WHERE
content IS NOT NULL
AND
TYPE != 3 ) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r1(x1)
WHERE
x.value('CommandType[1]', 'VARCHAR(8000)') IS NULL
--= 'CommandText'
ORDER BY
name
I have added a few things to your code sample.
1) Added the second Cross Apply "r1(x1)" (Line 30) to bring in the DataSet itself and then added the x1 object's @Name field (Line 15) for the dataset name.
2) For the command truncation issue, I've just changed your VARCHAR(50) to VARCHAR(8000)
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2011 10:30am
There is one issue: If a report has hot multiple datasets all sql selects are combined with every dataset ?
May 28th, 2011 6:07am
Yes, I had a bug in that code. I fixed the XML cross apply and I renamed a few of the aliases here as I was working it out. This should work for you:
WITH
XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
name
,
x.value('CommandType[1]', 'VARCHAR(8000)') AS CommandType,
x.value('CommandText[1]','VARCHAR(8000)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(8000)') AS DataSource,
q.value('@Name','VARCHAR(8000)') AS DataSetName
FROM
(
SELECT
name,
CAST
(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM
ReportServer.dbo.Catalog
WHERE
content IS NOT NULL
AND
TYPE != 3 ) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') d(q)
CROSS APPLY q.nodes('Query') r(x)
WHERE
q.value('CommandType[1]', 'VARCHAR(8000)') IS NULL
--= 'CommandText'
ORDER BY
name
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2011 10:50am
This topic is archived. No further replies will be accepted.
Other recent topics
