April 26, 2012 at 4:15 am
Guys,
We are refactoring database objects and in the process renaming SPs. At this point for SPs that SSRS reports use we are only renaming them, is there any to know via query from ReportServer database or from SSRS which RDL files use which SPs.
Any suggestions, inputs would help
Thanks
Report RDL FileName Dependent Database SP Name
April 26, 2012 at 5:08 am
i was looking through the threads a couple of days again and someone wanted an inventory of their SSRS server reports.
one poster pointed the OP to this url http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69257/
it contains a number of RDL files, one of which is a report for ReportQueries.rdl, this will loop through the RS db and show the commands within the data sets of the reports, I would recommend to download and store them somewhere as there look very useful in seeing whats happening/happened on SSRS.
but the command basically boils down to this
set transaction isolation level read uncommitted
;WITH ReportItems (ItemID, Name, Path, reportXml) AS
(
SELECT ItemID
, Name
, Path
, CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml
FROM Catalog
WHERE (Type = 2)
)
SELECT ItemID
, Name
, Path
, report.commandText.value('.', 'nvarchar(MAX)') AS commandText
FROM reportItems
CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //CommandText/text()') AS report(commandText)
UNION ALL
SELECT ItemID
, Name
, Path
, report.commandText.value('.', 'nvarchar(MAX)') AS commandText
FROM reportItems
CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //CommandText/text()') AS report(commandText)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply