March 8, 2011 at 10:43 am
I have the following query that runs on the ReportServer database and helps me get a list of all reports, report paths and datasource names in my SSRS-2008 server.
How can I enhance this query to also get the datasource paths (locations) in the report server?
--http://decipherinfosys.wordpress.com/2008/11/17/more-on-the-reportserver-db-queries/
;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
[PATH],
t.value('DataSourceName[1]', 'varchar(100)') as Data_Source
FROM (
SELECT
name,
[PATH],
cast(cast(content as varbinary(max)) as xml) as content_as_xml
from dbo.[Catalog]
WHERE Type = 2 and 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)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 29, 2011 at 7:18 am
Marios,
try to use this code:
;WITH XMLNAMESPACES (
DEFAULT'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd)
SELECTIV.name,IV.[PATH],t.value('DataSourceName[1]', 'varchar(100)') as Data_Source,DS.[PATH] as DataSourcePATH
FROM (
SELECTname, [PATH], cast(cast(content as varbinary(max)) as xml) as content_as_xml
fromdbo.[Catalog]
WHEREType = 2
and 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)
--New Relationship
--If there is two datasource with the same name, the result will duplicate
INNER JOIN dbo.[Catalog] DS on DS.Name = t.value('DataSourceName[1]', 'varchar(100)')
😉
Andressa Alves Martins
http://sqlgo.wordpress.com/
Twitter: Dre_Martins
April 4, 2011 at 2:25 pm
That works great, thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply