Method to Query SSRS data sources

Question

Is it possible to query SSRS report servers for all references to a specific server in data sources, embedded queries etc.? I have found some suggestions on Google, but the results I get don't seem to update after I change the embedded queries.

Answer 1

I know I'm late to the game here, but thought I would throw this out there. I needed to know how to determine what datasources were being used and how many reports were attached to it. This seems to fit my needs. Working off of previous answers, I came up with:

USE [ReportServer];
GO

SELECT DISTINCT
       cat1.Name AS cName,
       cat1.Path,
       (
           SELECT COUNT('x') AS counted
           FROM dbo.Catalog AS c
               JOIN dbo.DataSource AS ds
                   ON c.ItemID = ds.ItemID
           WHERE ds.Link = cat1.ItemID
                 AND c.Type = 2 --Report
       ) AS ReportCount
FROM Catalog AS cat1
WHERE cat1.Type = 5 --DataSource
ORDER BY cat1.Name;

Answer 2

Hi @Mark ,

after I change the embedded queries

I'm sorry i don't understand what you mean.
You could obtain the data source name corresponding to the report in the specified database through the following TSQL query:

USE [ReportServer]  
GO  
  
SELECT CATALOG.NAME  
    ,CATALOG.[Path]  
    ,DataSource.NAME datasource  
    ,CATALOG.[Description]  
    ,Created.UserName AS CreatedByUser  
    ,CATALOG.[CreationDate]  
    ,Modified.UserName AS ModifiedByUser  
    ,CATALOG.[ModifiedDate]  
FROM [dbo].[Catalog]  
LEFT JOIN (  
    SELECT [UserID]  
        ,[UserName]  
    FROM [dbo].[Users]  
    ) AS Created ON CATALOG.CreatedByID = Created.UserID  
LEFT JOIN (  
    SELECT [UserID]  
        ,[UserName]  
    FROM [dbo].[Users]  
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID  
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID  
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID  
WHERE CATALOG.[Type] = 2  
ORDER BY [Path]  
    ,NAME  

Output:
-- Connection strings of all SSRS Shared Datasources

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.  
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'  
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'  
     AS rd)  
,SDS AS  
    (SELECT SDS.name AS SharedDsName  
           ,SDS.[Path]  
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF  
     FROM dbo.[Catalog] AS SDS  
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource  
  
SELECT CON.[Path]  
      ,CON.SharedDsName  
      ,CON.ConnString  
FROM  
    (SELECT SDS.[Path]  
           ,SDS.SharedDsName  
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString  
     FROM SDS  
          CROSS APPLY   
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)  
     ) AS CON  
-- Optional filter:  
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'  
ORDER BY CON.[Path]  
        ,CON.SharedDsName;  

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi Joyzhao-MSFT,

It doesn't seem to be letting me add comments above.

Thanks for the response - after reading back my initial post I see it's not very clear what I am asking. I'll try again with an example.

Our mobile working solution has a report server database on the same server as it's database. But the mobile working solution integrates with various other systems and those databases are on different servers. We have a lot of SSRS reports, and the datasets in them are embedded in a lot of cases by the look of it.

I found a query I could use to extract the XML from the embedded reports, and search for the term "Live Server 1.dbo.TableName" so that I know how many I need to change to "Test Server 1.dbo.TableName".

However, when I update "Live Server 1.dbo.TableName" to "Test Server 1.dbo.TableName, and then run the query again, it still pulls back the SSRS report I updated and still says it has "Live Server1.dbo.TableName referenced, but I can see by the data the report pulls back that it's referencing the correct server now.