0

We have modified the DataSource in the web, but when pulling the datasource information from TSQL script we find the 1st connection string not the new modified connection string. I have researched and found others that have this issue, where are the changes for the connection string house? In what database and what shall we filter to get the most recent connection string?

Thank You Elizabeth

 drop table #tmp
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
/*
https://gallery.technet.microsoft.com/scriptcenter/List-connection-strings-of-1a9a9adc
*/
(
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
)

, Depend AS
/*
https://stackoverflow.com/questions/9638431/listing-all-data-sources-and-their-dependencies-reports-items-etc-in-sql-ser
*/
(
SELECT
   C2.Name AS Data_Source_Name,
   C.Name AS Dependent_Item_Name,
   C.Path AS Dependent_Item_Path
FROM
   ReportServer.dbo.DataSource AS DS
   INNER JOIN ReportServer.dbo.Catalog AS C
     ON DS.ItemID = C.ItemID
       AND DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog WHERE Type = 5) --Type 5 identifies data sources
   FULL OUTER JOIN ReportServer.dbo.Catalog C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
)
SELECT
  @@SERVERNAME AS 'ServerName'
  , CON.[Path]
  , CON.SharedDsName
  , CON.ConnString
  , Depend.Dependent_Item_Path
  , Dependent_Item_Name into #tmp


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
LEFT JOIN Depend ON CON.SharedDsName = Depend.Data_Source_Name
-- Optional filter: 
WHERE CON.ConnString like '%RST-SQLCLSTR%'
--LIKE '%one-%' 
ORDER BY CON.[Path] 
        ,CON.SharedDsName;

 select * from #tmp where connstring like '%tito%'

select shareddsname from #tmp
group by SharedDsName
Elizabeth
  • 11
  • 2
  • Please show us the query that is not working out as expected – Wouter Sep 21 '20 at 14:19
  • I also found this https://dba.stackexchange.com/questions/138236/get-ssrs-datasources-from-reportserver so it looks like a glitch with ssrs 2016 wondering if anyone has a way to find the updated information – Elizabeth Sep 23 '20 at 12:51
  • Are you using a shared data source? Or did you change it to a data source in the report itself? – Wouter Sep 23 '20 at 14:16
  • It is a shared datasource from the web, then I changed the datasource name and connection string on the web, all works properly, the dependants all changed properly and the connection is working fine, it is when I run the sript against the reportserver database that shows the wrong conneciton string, it shows the original connection string only not the updated one. – Elizabeth Sep 23 '20 at 14:43
  • It looks like this is a known bug with SSRS 2016 per the url I attached, but I am hopeing someone has found how to retreive the current connection string. We are in the process of moving items from 1 server to another and would like to run the script to verify the changes needed and that they were successfull , instead of having to manually check each. – Elizabeth Sep 23 '20 at 14:44

0 Answers0