Want to view a list of queries used in reports deployed on a Microsoft SQL Server Reporting Services (SSRS) server?
While SSRS’s web service exposes the functionality necessary to assemble this information, using that service requires programming/scripting skills or third-party software. Thankfully, these requirements can be bypassed by fetching this information directly from the report server database.
The below query returns query text and data source details for each local data set in each deployed report. The concepts covered can be extended to include shared data sets; however, implementing this is left as an exercise to the reader. (That’s the sophisticated way of saying “when I wrote this article, I only had access to SQL Express—which doesn’t support shared data sets—and so couldn’t test how to access their details.”)
Caveat: Microsoft doesn’t document or officially support querying the tables used below. Their schema and how data stored in them is interpreted can change between SSRS versions. If the query provided below doesn’t work on your version of SSRS, this could be why.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
-- Provided "as is" with no warranties of any kind. User assumes all risks of use. /* The XML XQuery statements used below ignore namespace so that information can be retrieved from multiple RDL versions. */ WITH CatalogWithXml AS ( -- XMLifyies Catalog's Content column. /* For report (Type = 2) and shared data source (Type = 5) objects, the image-typed column Content stores the XML RDL defining the object. We convert this column to XML so that SQL's XML type's functions can be used on it. */ SELECT *, ContentXml = ( CONVERT (XML, CONVERT (VARBINARY( MAX ), Content))) FROM Catalog WHERE Type IN (2, 5)
), SharedDataSources AS ( -- Details on uses of shared data sources. -- * Unused data sources are ignored. -- * ItemID identifies the catalog entry (e.g. report) using the shared data source. It is not -- the data source's ID! /* Table DataSource contains a row for each data source (embedded or shared) used in each report. Its column Name stores the data source name, as defined in the report. Shared data sources are defined (RDL XML) in the catalog. Inner joining between these two tables limits this CTE's resultset to details on shared data sources because embedded data sources don't have Link-ed rows in the catalog. */ SELECT ds.ItemID, SharedDataSourceName = c. Name , LocalDataSourceName = ds. Name , DataProvider = ContentXML.value( '(/*:DataSourceDefinition/*:Extension)[1]' , 'NVARCHAR(260)' ), ConnectionString = ContentXML.value( '(/*:DataSourceDefinition/*:ConnectString)[1]' , 'NVARCHAR(MAX)' ) -- Each DataSource row with a Link value represents a use of a shared data source. FROM DataSource ds -- Uses the Link value to look up the catalog entry defining the shared data source. JOIN CatalogWithXml c ON ds.Link = c.ItemID ), AllDataSources AS ( -- Details on both embedded & shared data sources *used* by reports. /* Embedded data sources are defined in the hosting report 's RDL. Shared data sources are referenced (but not defined) in this RDL. We extract the relevant details and then join to the SharedDataSources CTE to assemble a resultset with details on each data source (embedded and shared) used by each report (identified by ItemID). */ SELECT r.ItemID, r.LocalDataSourceName, -- embedded data source' s name or local name given to shared data source sds.SharedDataSourceName, SharedDataSource = CAST (( CASE WHEN sds.SharedDataSourceName IS NOT NULL THEN 1 ELSE 0 END ) AS BIT ), DataProvider = ISNULL (r.DataProvider, sds.DataProvider), ConnectionString = ISNULL (r.ConnectionString, sds.ConnectionString) FROM ( SELECT c.*, LocalDataSourceName = DataSourceXml.value( '@Name' , 'NVARCHAR(260)' ), DataProvider = DataSourceXml.value( '(*:ConnectionProperties/*:DataProvider)[1]' , 'NVARCHAR(260)' ), ConnectionString = DataSourceXml.value( '(*:ConnectionProperties/*:ConnectString)[1]' , 'NVARCHAR(MAX)' ) FROM CatalogWithXml c CROSS APPLY ContentXml.nodes( '/*:Report/*:DataSources/*:DataSource' ) DataSource(DataSourceXml) WHERE c.Type = 2 -- limit to reports only ) r LEFT JOIN SharedDataSources sds ON r.ItemID = sds.ItemID AND r.LocalDataSourceName = sds.LocalDataSourceName ), DataSets AS ( -- Details on data sets used in reports. /* Outputs one row per data set used in each report. */ SELECT ItemID, DataSetName = QueryXml.value( '@Name' , 'NVARCHAR(256)' ), DataSourceName = QueryXml.value( '(*:Query/*:DataSourceName)[1]' , 'NVARCHAR(260)' ), CommandType = QueryXml.value( '(*:Query/*:CommandType)[1]' , 'NVARCHAR(15)' ), CommandText = QueryXml.value( '(*:Query/*:CommandText)[1]' , 'NVARCHAR(MAX)' ) FROM CatalogWithXml CROSS APPLY ContentXml.nodes( '/*:Report/*:DataSets/*:DataSet' ) QueryData(QueryXml) ), Data AS ( -- Combines data set and data source details with additional information from Catalog. SELECT ds.ItemID, Name , Path, LocalDataSourceName, SharedDataSource, SharedDataSourceName, DataProvider, ConnectionString, DataSetName, CommandType = ISNULL (CommandType, 'Text' ), -- "Text" = default command type CommandText FROM DataSets ds JOIN AllDataSources src ON src.ItemID = ds.ItemID AND src.LocalDataSourceName = ds.DataSourceName JOIN Catalog c ON ds.ItemID = c.ItemID ) SELECT * FROM Data |
Very nice post. Fully loaded with information.
Ben, this code is wonderful, however, I do miss the shared datasources. Have you come any further on this or could you help me to add shared datasets to this? Friendly greetings, Henro
Great question! Slow progress is being made on a shared datasets-included version.
when a report is uploaded, the data source information also gets stored in the dbo.Datasource table (and items get created in the catalog, and the report and data source catalog items get associated in the dbo.Datasource table). If the data source information matches up with an existing shared data source, that’ll get used. Or, if you need to manually link a report to a previously stored datasource, then the report gets linked to stored data source catalog items (and the credentials, connection strings, etc.), and they’re linked together in the dbo.Datasource table.
The username, password and connection string stored in the dbo.Datasource table are stored in Credential objects that only the SSRS service can interact with (e.g., decrypt).
With the Powershell ReportingServicesToolkit module, you can at least ping the SSRS proxy service to get you some of the decrypted connection string and username info from each datasource. Or, use the module to identify the SSRS REST call to make, and then look up the details on the REST call from Microsoft for details on it.
Thanks, Corey.
Really useful! Thanks for this.
Came across your post while searching for ways to locate reports that tries to connect with a specific login.
This helps narrow it down by showing where they connect to and whether they have embedded connections or not :).
First for others, when I used this I got an XML parsing error for unrecognized input signature. This is due to some of the images we have in our system. I just had to add a filter for type (type 3) in the XML XQuery statement.
On another note, I have been walking down a similar path when I found this post. I noticed something a little odd. When i query the connection string of datasources in the catalog i get references to servers and catalogs when initially created. However the web gui, has a completely different connection string. I was attempting initiate a process to point reports from a DEV environment to a TEST environment. Has anyone had success doing this other than the gui?
thank you
it help me lot thank you
Very helpful.
Thanks for this excellent resource. It has helped me a lot with a project I’ve been on.
This is great except its not working for me in SSRS2016. I guess the XML has different structure. Is there an updated version for 2016?
Great question! Unfortunately, I don’t know the answer—I haven’t had to audit report queries in SSRS 2016 yet. 🙂
I have tried to run this on SQL2008 and get the following error.
Msg 9420, Level 16, State 1, Line 4
XML parsing: line 1, character 3, illegal xml character
Does it not work on 08? What am I doing wrong?
This is strange. The error indicates that SQL Server wasn’t able to parse SSRS’s XML. I’d be curious whether the problem is systemic or just with a few rows in table Catalog.
Could you try running something like the below, adjusting the
WHERE
clause on each run until you’ve isolated the rows having this problem (i.e. adjust theWHERE
clause until all problematic rows are excluded and the query runs fine)?Thanks for publishing this. I think I found what is causing the issue. It seems the majority of records where the column MimeType is NOT null are causing the error below:
on this statement:
ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))
On our server all records with the following MimeTypes caused the error:
image/png
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
application/json; charset=utf-8
Half of the records with the following MimeType failed:
application/octet-stream
All of the records with the following MimeType worked:
application/xml; charset=utf-8
Sadly, I don’t know the fix.
So I have added :
WHERE Type <> 3
In the first part of the query to filter the Type 3 (application/octet-stream and image/png) in the query and it works for me (SSRS2016). The first part of the query is now as follow:
Really like your script Ben but I also run into the same error. Hope you can provide a solution.
In case it helps anyone, I got this error:- XML parsing: line 5, character 18, A string literal was expected
Which I was able to fix by adding a filter to ignore .odc datasets