List connection strings of all SSRS Shared Datasources

Transact-SQL script to get connection string of all SSRS Shared Datasources.

 
 
 
 
 
4.8 Star
(5)
4,485 times
Add to favorites
Databases
8/19/2011
E-mail Twitter del.icio.us Digg Facebook
  • Null connection string column
    1 Posts | Last post March 11, 2015
    • Hello Olaf
      
      This query was working till yesterday in all my SSRS environments, now the connection string information appears to be NULL, I think maybe it was a change in the XML namespace ?? Could you please provide a solution for this ? Thanks in advance!
  • Hi Olaf
    1 Posts | Last post May 16, 2014
    • I appreciated your script but i have a question for you. 
      In SSRS 2012 (from report manager) when i change connection string of shared datasource and i execute your code i obtain the old version of connection string.
      Seems the Catalog table mantains only the first version of connection string while the updates are stored in Datasource table (ConnectionString column)  but are crypted.
      Now the question is: how can i obtain the last shared datasource connection string?
      Thanks in advance
  • Database Name
    2 Posts | Last post August 24, 2011
    • Great code, on this and your other scripts against the SSRS metadata!  To run this one without assuming the current db is the Reporting Services db, change line 10 
      from:
           FROM dbo.[Catalog] AS SDS
      to:
           FROM ReportServer.dbo.[Catalog] AS SDS
      
      (using the default database name for the SSRS database)  Would you prefer to include this in the main script?  
      Hope this helps.
    • Hello Chris,
      Thank's for your reply and I am glad to hear that my script's are also valuable for other then for me.
      
      In my first version I used the three-part-qualifier [ReportServer].db​o.[Catalog], but then I removed it because the database is not fix.
      
      See, I have a few SSRS instances running and all repository databases are hosted on one SQL Server instance and in this constellation by default the database name get the instance name as suffix like:
      ReportServer
      ReportServer$Instance1
      ReportServer$Instance2
      and so on. In this cases you would have to remove the fix database name "ReportServer" to get it working for all instances.
      
      Actually I am working on a script which also uses the ReportServerTemp database and then I have to use three-part-qualifier; but then I will note to may change the database names in the script.
      
      Bye, Olaf.