Listing out connection strings of all SSRS Shared Data Sources

Moving a Report database to an other SQL Server can be tricky at times especially when there are multiple data sources around and which one should be rightly changed afterwards.
With this query for ReportServer database you get the connection string of all Shared Data Sources to document the usage or to search for a specific server/database.

–Listing out connection strings of all SSRS Shared Data Sources
;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;

 

Happy Learning!

Cheers!
Aman Kharbanda

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s