Systems Engineering and RDBMS

ReportServer Database Queries

Posted by decipherinfosys on November 16, 2008

As you might already know, the RDL files (Report Definition files for SSRS) are stored in the ReportServer database (f you have chosen a different name at install time like ReportServerQA or ReportServerDev, then it will be in that database). This database has a table called Catalog which stores this information. A simple:

sp_help catalog

will give you all the information on that table and as you start going through the names (parameter, executiontime etc.), it will all start making sense. However, when you look at the data in the content column, you will see that it is stored as an image data type. Not very useful, is it? How can we extract out information from this column so that it is meaningful to us? We would first need to convert the image data type to varbinary and then can convert it to an XML data type and then query on it in order to get the information that we need. Here is how we can achieve this:

select
name,
cast(content as varbinary(max)) as content_as_binary,
cast(cast(content as varbinary(max)) as xml) as content_as_xml
from Catalog
WHERE content is not null
and name not like ‘%gif%’

Using this code, you will see that you will get the name of the report, the content transformed from image to binary and the content transformed into an XML – if you look after the transformation into XML, you will see that you can then apply the namespaces and extract out the information using this SQL:

;WITH XMLNAMESPACES (
DEFAULT
http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition’,
http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd
)
SELECT
name,
i.value(‘CommandType[1]‘, ‘VARCHAR(100)’) AS CommandType,
i.value(‘CommandText[1]‘, ‘VARCHAR(2000)’) AS CommandText
FROM (
select
name,
cast(content as varbinary(max)) as content_as_binary,
cast(cast(content as varbinary(max)) as xml) as content_as_xml
from Catalog
WHERE content is not null
and name not like ‘%gif%’
) as IV
CROSS APPLY content_as_xml.nodes(‘/Report/DataSets/DataSet/Query’) rpt(i)

In this SQL, we used the original SQL shown above where we converted the image data type –> binary and then to XML and at the top of that we did a CROSS APPLY using the nodes() method and extracted the name of the report, the command type and the command text. In scenarios where a stored procedure is used, the commandtype will contain “StoredProcedure” in it and the CommandText will have the procedure name – in case of adhoc SQL, you will have Null in the commandtype column and will have the SQL text in the commandText field. The name not like ‘%gif%’ was used to eliminate an embedded image file that we had on our server.

So, in this post you have seen how to get some meaningful information out of the catalog table stored under the ReportServer database. Using this, same set of SQL, you can also extract out other pieces of information like the Data Sources by using the nodes() method and simple XML that we have shown in our blog posts before. We will cover that in a post tomorrow. Using these SQLs, you can see all this information in a concise fashion in one single place rather than going through the ReportManager and looking at the information one by one.

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: