BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources
Hi Folks,
In this article we are going to cover How to List connection strings of all SSRS Shared Data sources.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- Input Parameter of SQL Script
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
How to find List of connection strings of all SSRS Shared Data sources?
Description of SQL Script:
Let’s say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources, to document the usage or to search for a specific server/database.
SQL Script Output Column
Input Parameter of SQL Script
Directly execute the script on Report Server Database.
SQL Script Code
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;
SQL Script Output Screenshot
User Level to execute
300
Hope you will like How to List connection strings of all SSRS Shared Data sources.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 
I’m finding that once I change the connection string for a shared connection and then run the query again, the data hasn’t changed in the Reporting Services database. Why is that?
We need to deploy solution again also you can create small asp.net SSRS as Report Viewer to debug your ssrs solution !
@Vishal: First of all: wonderfull post!
would it be possible to add information concerning the credentials? More specifically, could this be expanded to include the username that is used in the datasource?
Sure we can ..
Go to this link for more details on it
http://msdn.microsoft.com/en-us/library/ms156450.aspx
AND
http://windows-scripting.org/2011/06/22/manage-permissions-sql-server-reporting-services/
Still if you didnt find anything .. mail me requirement will give you solution !
If I wanted to see all connections strings not just the Shared Data Sources how would i modify this code. I tried by simply changing the type its looking for or just taking it out. It always wants to return type = 5. Any ideas? I am trying to convert hard coded server names to DNS as we have migrated lots of servers and there are reports that weren’t changed at the time.
You can try bulk edit xml SSRS rdl file using C#..
Hi Vishal – Is there a way to write an Update SQL script code that will update the ConnectionString?
https://haseebmukhtar.wordpress.com/2011/11/09/dynamic-database-in-ssrs-2008/ this link might help you Rahul
Thanks Vishal for that quick info. But my requirement is slightly different. I have an autosys job that would call a bat file on windows server which in turn would connect to the SSRS ReportServer DB and update the Datasource DB server in the connection string.
I tried to explore on the update script but had no luck. Came across this forum where I saw a SELECT being written to get the ConnectionString and thought that we can even write a UPDATE in that case. Let me know if it is possible to write an UPDATE and any tips would be helpful.
My understanding is that once I update the DS DB server in the SSRS ReportServer all the reports making use of this shared datasource would start connecting to the changed DB server.