5

We have migrated from SSRS 2012 to SSRS 2016, and moved it to a new domain.

Using below code, we are able to list all datasources and their connection strings. How can we change the connection strings of nearly 700 reports using PowerShell or T-SQL? (so that the point to new data warehouse server)

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

You can use the replace value of XML DML command to modify the value stored in the connection string. I have put together an example below for you as a guide but please note that it is untested as I do not have access to a report server at the moment, it should at least point you in the correct direction:

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;

More information regarding the replace value of command can be found at Microsoft Docs.

| improve this answer | |
2

Often, a DNS CNAME is the simplest solution.

If your connection string uses a simple machine name (i.e., server as opposed to a fully qualified name like server.our_company.com), and the old name isn't an existing device in your new domain, then you can set up the old name as DNS alias (canonical name, or CNAME) to the current name.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.