Systems Engineering and RDBMS

More on the ReportServer DB Queries

Posted by decipherinfosys on November 17, 2008

Yesterday, we had covered how to glean meaningful information from the Catalog table contents in the ReportServer database for SSRS reports. We had seen how to convert the image data type to XML and apply the nodes() method to it along with the CROSS APPLY operator to extract out meaningful information.

Another scenario that you might have faced in some of the shops would be if there are multiple data sources (many different instances for different application databases) and there is one single reporting server (which is the case most of the time), then having a query to see all the data sources at one time via a simple query can help the developer/DBA to troubleshoot issues.

Here is such a query – it adds on to the query that we had shown yesterday and adds more information to it:

;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(‘@Name[1]‘, ‘varchar(100)’) as Data_Set,
t.value(‘DataSourceName[1]‘, ‘varchar(100)’) as Data_Source,
Replace(t.query(‘data(QueryParameters/QueryParameter/@Name)’).value(‘.’, ‘varchar(100)’), ‘ ‘, ‘, ‘) as Query_Parameters
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’) rpt(i)
CROSS APPLY i.nodes(‘Query’) as rpt2(t)

The query uses the nodes() method and the CROSS APPLY operator and also uses the query method to generate a comma separated list of the parameters used in the report.

About these ads

2 Responses to “More on the ReportServer DB Queries”

  1. [...] an untyped XML to us.   We had covered this in our post on the ReportServer DB Queries – here.  And the last method in the list [...]

  2. Marios Philippopoulos said

    Hi,

    Is it possible to enhance the query to also retrieve the datasource paths?

    Thank you,
    Marios Philippopoulos

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 77 other followers

%d bloggers like this: