從 ReportServer 獲取 SSRS 數據源

舊 SSRS 上的內務管理，此特定伺服器是 2008R2。

我有下面的查詢，它為我提供了所有報告的名稱、位置、創建和修改概述。我還想至少包含“連接字元串”，如果可能的話，還想從數據源屬性中包含一些其他詳細資訊。但我在 ReportServer 中找不到它。我看到人們使用 PowerShell 將其從 SSRS Web 服務中取出，我希望找到一個 T-SQL 解決方案。

如何使用 SQL 查詢顯示數據源詳細資訊？

--List all the reports on SSRS via "ReportServer" database 

USE [ReportServer]
GO
SELECT Name
--, [ItemID] --Primary key
, [Path]
, [Description]
--, [CreatedByID] --need link to get anything usable from here
, Created.UserName as CreatedByUser
, [CreationDate]
--, [ModifiedByID] --need link to get anything usable from here
, Modified.UserName as ModifiedByUser
, [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

WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html

ORDER BY [Path], Name

從 ReportServer 獲取 SSRS 數據源

我還想至少包括“連接字元串”

如何使用 SQL 查詢顯示數據源詳細資訊？

用於獲取 SSRS 數據源的連接字元串的 TSQL

-- Transact-SQL script to get connection string of all SSRS Shared Datasources.

/*
Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
to document the usage or to search for a specific server/database.

Please remark: Querying the ReportServer database directly is not a supported way.
Works with SSRS 2005 and higher version ReportServer databases.
Requieres select rights on the "Catalog" table in ReportServer database.
*/

-- 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;

這是一個使用 TSQL 添加數據源名稱的查詢：

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

引用自：https://dba.stackexchange.com/questions/138236