August 14, 2012 at 11:46 am
Is there anyway to find out if a particular table is being used by a report on the reporting server?
August 14, 2012 at 12:27 pm
For Ad hoc queries you can query catalog table:
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT name
, x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType
, x.value('CommandText[1]','VARCHAR(50)') AS CommandText
, x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
SELECT name
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM Catalog
WHERE content IS NOT NULL
AND type != 3) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') IS NULL
ORDER BY name
for stored procedures:
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT name
, x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType
, x.value('CommandText[1]','VARCHAR(50)') AS CommandText
, x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
SELECT name
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM Catalog
WHERE content is not null
and TYPE = 2) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
ORDER BY name
August 16, 2012 at 1:48 pm
Can't find the "Catalog" table. Does your script require an add-on?
August 16, 2012 at 1:52 pm
If you don't have the Catalog table in your ReportServer database, something is wrong... Are you sure you are running this on the right server?
Jared
CE - Microsoft
September 21, 2012 at 2:37 pm
We've been using this method to do impact analysis, searching for specific text in RS dataset queries. Our little world started in SQL Server 2000, then 2005, now 2008R2. We recently realized that this method wasn't returning all the reports containing certain query text, and from there determined that the RDL's in the reportserver catalog are based on different xml namespaces and would call for different values in the WITH XMLNAMESPACES statement. Validated this by manually changing the XMLNAMESPACES statement, but we're looking for a method to query across all the records in the catalog table using the correct namespace for each record. With around 1000 reports, we've found four namespaces in use:
http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition 1%
http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition 66%
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition 23%
http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition 10%
Once we extract the namespace value from each catalog record, I can imagine using a cursor and dynamic SQL to loop through each namespace and the associated records. But is there a way to use multiple namespaces in one WITH XMLNAMESPACES statement?
In a related question, did we maybe miss a step in our migrations, ending up with multiple historial namespace values in different reports, where migration to a new version should have converted all the reports to the most recent namespace? Or might this be caused by new report development being done in different versions of Visual Studio/BIDS?
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply