SQL Server Mentalist

BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources

September 25, 2013 7:00 am

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 :

Link Resource Website

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

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Posted by Vishal Pawar

Categories: Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar

Tags: DBA, Fast SQL, Microsoft SQL Server, SQL Help, SQL Mentalist, SQL Script, SQL Server, SQL Server 2008 R2, SQL Server 2012, SQL tips, SQL Tricks, TSQL, Vishal Pawar

« Older Newer »

10 Responses to “BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources”

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?

By Tony Clark on May 29, 2014 at 10:40 pm
1. We need to deploy solution again also you can create small asp.net SSRS as Report Viewer to debug your ssrs solution !
  
  By Vishal Pawar on June 8, 2014 at 9:33 pm
@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?

By Henro Veijer on June 5, 2014 at 1:24 pm
1. 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 !
  
  By Vishal Pawar on June 8, 2014 at 9:31 pm
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.

By CT on October 2, 2014 at 9:37 pm
1. You can try bulk edit xml SSRS rdl file using C#..
  
  By Vishal Pawar on October 27, 2014 at 7:30 am
Hi Vishal – Is there a way to write an Update SQL script code that will update the ConnectionString?

By Rahul on June 9, 2015 at 5:30 pm
1. https://haseebmukhtar.wordpress.com/2011/11/09/dynamic-database-in-ssrs-2008/ this link might help you Rahul
  
  By Vishal Pawar on June 9, 2015 at 10:12 pm
  1. 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.
    
    By Rahul on June 11, 2015 at 6:28 am
  2. 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.
    
    By Rahul on June 11, 2015 at 7:11 am

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.