SSRS 2016 - 更改所有報告中的連接字元串

我們已從 SSRS 2012 遷移到 SSRS 2016，並將其移至新域。

使用下面的程式碼，我們可以列出所有數據源及其連接字元串。我們如何使用 PowerShell 或 T-SQL 更改近 700 個報表的連接字元串？（以便指向新的數據倉庫伺服器）

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;

您可以使用replace value ofXML DML 命令來修改儲存在連接字元串中的值。我在下面為您整理了一個範例作為指導，但請注意，它未經測試，因為我目前無法訪問報表伺服器，它至少應該為您指明正確的方向：

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 [SharedDsName], [Path], [DEF]
 INTO #catalog
 FROM SDS;

UPDATE #catalog
  SET DEF.modify ('
   declare namespace rds="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource";
   declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
   replace value of (/rds:DataSourceDefinition/rds:ConnectString/text())[1]
   with "new connection string"
');

UPDATE a
  SET a.[Content] = CONVERT(image, CONVERT(varbinary(max), b.[DEF]))
 FROM dbo.Catalog AS a
 JOIN #catalog AS b
   ON a.[Name] = b.[SharedDsName]
  AND a.[Path] = b.[Path];

DROP TABLE #catalog;

replace value of可以在Microsoft Docs中找到有關該命令的更多資訊。

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