Monday, July 19, 2010

How to query for SQL text in rdl stored in reportserver catalog table

Ever make a change to your database and wonder what reports are dependant upon
it?  If you use stored procedures / views in your reports you can query the
system catalogs.  But if you are using inline SQL you have to root through
the RDL's.  The report content is an image field which means you have to
convert it to varbinary (max) then to Xml.  From there you can easily look
for your SQL.  For different versions of Reporting services use the
corresponding namespace  (found at the top of the RDL file).

with xmlnamespaces 
  ( '' AS REP ) select * from (   select c.Path,

     DataSetXML.value('@Name','varchar(MAX)') DataSourceName,
     DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)') CommandText
    (select ItemID,
            CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
     from dbo.Catalog
    ) ReportXML 
  CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML (DataSetXML)
  INNER JOIN dbo.Catalog c ON ReportXML.ItemID = c.ItemID
) tbl
where CommandText like '%search criteria goes here...%'

No comments:

Post a Comment