Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources

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

image

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

image

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 |

Advertisements
  1. Tony Clark
    May 29, 2014 at 10:40 pm

    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?

    • June 8, 2014 at 9:33 pm

      We need to deploy solution again also you can create small asp.net SSRS as Report Viewer to debug your ssrs solution !

  2. Henro Veijer
    June 5, 2014 at 1:24 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?

  3. CT
    October 2, 2014 at 9:37 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.

    • October 27, 2014 at 7:30 am

      You can try bulk edit xml SSRS rdl file using C#..

  4. Rahul
    June 9, 2015 at 5:30 pm

    Hi Vishal – Is there a way to write an Update SQL script code that will update the ConnectionString?

    • June 9, 2015 at 10:12 pm
      • Rahul
        June 11, 2015 at 6:28 am

        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.

      • Rahul
        June 11, 2015 at 7:11 am

        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.

  1. No trackbacks yet.

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

%d bloggers like this: