February 20, 2011 at 7:51 am
Is there a way to find which reports are dependant on a specific stored procedure? I can't find anything in the report server database but it might be found else where?
February 21, 2011 at 7:35 am
The information is stored in the Reporting Services database but I believe it is stored in a binary format, making it a little more difficult to get at. Without digging or researching at all I believe it is in the Content column of the Catalog table, but that is really just a guess.
The low tech way would be to do a Windows search for files containing the text of the name of the stored procedure and then point it at the folder(s) where you have your rdl files stored.
February 21, 2011 at 12:07 pm
try 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
into #temp
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)
select * from #temp where commandText like '%sp%'
drop table #temp
February 21, 2011 at 1:18 pm
Thanks Daniel I looked in the catalog table before posting but couldn't see anything readable so i resorted to windows searching the rdl files. Unfortunately there are far too many files scattered across the organisaion. It would be easier if the rdl files where all in one place, I am incline to think they are all together somewhere on the report server (but i don't know where they would be, does rs store them in a directory on the report server when you click deploy? or just convert to xml?)
MaricopaJoe this looks exactly like the kind of thing I need, I will give it ago and lt you know the outcome.
Thank you both for all your help.
Dale
February 21, 2011 at 2:43 pm
Thanks Joe. I had seen this (or something like it) before, but I didn't have it in my code library and I didn't have the time to work it out. It is in my code library now. 🙂
February 21, 2011 at 5:22 pm
Good. Glad to help. It actually is from this link, which u may find usefull.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply