More on the ReportServer DB Queries

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