主页 / dba / 问题 / 189265
Accepted
user3657339
Asked: 2017-10-26 01:09:24 +0800 CST

SSRS 2016 - 更改所有报告中的连接字符串

  • 772

我们已从 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;
ssrs ssrs-2016

2 个回答

  1. Best Answer

    您可以使用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中找到有关该命令的更多信息。

    • 4

  2. 通常，DNS CNAME 是最简单的解决方案。

    如果您的连接字符串使用简单的机器名称（即，server与完全限定名称相反server.our_company.com），并且旧名称不是新域中的现有设备，那么您可以将旧名称设置为 DNS 别名（规范名称或 CNAME) 到当前名称。

    • 2

相关问题