How to get details of data sources and associated command with reports on Report Server

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’,
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ AS REP
)
SELECT c.Path ,c.Name ,DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM ReportServer_SQL2008_Prod.[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) ) LIKE ‘%quicktrend%’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s