Last time, in what seems like my endless march towards documenting SSRS systems in a thorough way, I explained that I was going to go low-level to find out what SSRS artifacts my automation identity did not have privileges to read. If I didn’t know what objects on the server would cause a problem, as I trolled through the server contents to document it, how could I fix those privileges?
I mentioned that the individuals who architect the SSRS system don’t like us to go into the base ReportServer tables to do stuff like this. We’re not supposed to. It’s not approved.
But the non-approved way is darn useful.
And, ladies and gents, we’re going to do it again.
The only reason I had to know about the privileges for an automation identity was that I was going to use an automated method to call SSRS web service methods on every object, to document each one via the web service API, which is The Approved Way.
Suppose we just don’t care?
If we don’t mind going straight to the ReportServer tables, we can read all the RDLs directly from the Catalog, in one neat SQL statement – no web service calls required.
Basically, just do this:
go
select Path,
cast(cast(Content AS varbinary(max)) As xml) as rdl_xml,
— if you prefer non-xml results, use varchar (nvarchar won’t work):
cast(cast(Content AS varbinary(max)) As varchar(max)) as rdl_text
from dbo.Catalog
where content is not null and type = 2
Et voilà
Sure, that just gives us the RDLs (type=2), but those report definitions are the lion’s share of what I was after; from there, parsing out the command texts so that we can find embedded query dependencies is duck soup. This task is the most critical reason to document SSRS installations thoroughly, IMHO. (Finding out the rest of the object types’ contents from the Content field isn’t too hard to figure out in similar syntax anyway, I bet, if you want to do it.)
And, sure it’s naughty. MS would rather we use the web service calls. You can sympathize. We’re tracking mud all over their nice clean architected floor.
But going to the base tables of Report Server is a heck of a lot faster and more SQL-friendly. Some days, that just feels good.