I would like to retrieve the connection string (and credentials) for each of my data sources (without having to click on each individual item). The DataSource table has a column called ConnectionString, but the data type is image, which cannot be converted to varchar. Does anyone have any ideas of how to pull this information from the database?I have tried converting to binary/varbinary, and then converting to varchar, but I get garbage back.Note: I am currently using SSRS 2005Thanks,Marianne

Am I correct in saying thatit's not feasable to go to the properties of each datasource and see the connection string that way?Martyn

Yes, you are correct. I would like to do this is a query so that I can quickly see each data source and its connection information.Thanks,Marianne

Hi Marianne, The information that stored in the Report Server database is encrypted. And the structure of the Report Server database is undocumented. So I recommended that do not access the Report Server directly. The SQL Server Reporting Services has offered a interface that used to manger the Report Server. The interface is SQL Server Reporting Services web services. In this case, we can use the ListChildren method and the GetDataSourceContents method to get all the data sources and their information. I have implemented a report script file, the file is available on: http://cid-3c7e963ff6ccd974.skydrive.live.com/self.aspx/.Public/GetPropertiesOfDataSources.rss We can get all of the data sources and their information by executing the script file using the following command: rs i GetPropertiesOfDataSources.rss s http://<reportserver name>/reportserver For more information about SQL Server Reporting Services web services, please see: http://msdn.microsoft.com/en-us/library/cc282207.aspx Any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

Hi Jin Chen, If at all I wanted to update the connection string for a specific report based on the requirement dynamically, I will have to update the rdl file to get that done. I have a ssrs report deployed and wanted to change the database name dynamically. It should not be interactive. For instance, I have 3 databases as ( DBtest0131, DBtest0228 and DBtest0331 ) . They are monthly snapshops. Everytime I execute, the connection string should check the today's date and and execute the report against the correct snapshot. To acheive this, I have written a .net console app that updates the below rdl tag and saves the same file. This way, we can dynamically change the connection string in rdl and persist it. Later, when we need to run through the report, it picks up the new db connection. It has worked fine. But, I wonder if there is a easy way to get it done with a piece of t-sql code that updates the db connection string. As we know the connection string dbo.Datasource table in report server is totally encrypted. Example The report has the connection string as below for Jan month. Data Source=SESH-BEJAWADA\MS2008;Initial Catalog=DBTest0131 When it aproaches feb month, it should be updated as Catalog= DBtest0228. Please kindly msg me to abbi1680@gmail.com Thanks Abbi

Hi Jin, I downloaded your script and was impressed with the information it retrieves, however, it is not formatted very well with the name of each data source being on the same line as the previous data source so I updated the script to create a blank line between each data source block, and also to include the login credentials used by the data source, and the path for the data source, which is very useful in being able to locate the data source for editing in Report Manager. However, it is not retrieving all data sources and I don't know why. I know this for a fact because there are data sources defined that are not in the output of this script but I don't know enough about VB to improve the script further, or how the report server is configured to ensure the code finds the missing data sources. Here's the updated script. '============================================================================= ' File: PublishSampleReports.rss ' ' Summary: Demonstrates a script that can be used with RS.exe to ' publish the sample reports that ship with Reporting Services. ' '--------------------------------------------------------------------- ' This file is part of Microsoft SQL Server Code Samples. ' ' Copyright (C) Microsoft Corporation. All rights reserved. ' ' This source code is intended only as a supplement to Microsoft ' Development Tools and/or on-line documentation. See these other ' materials for detailed information regarding Microsoft code samples. ' ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A ' PARTICULAR PURPOSE. Public Sub Main() rs.Credentials = System.Net.CredentialCache.DefaultCredentials 'Create the parent folder GetCatalogItems(String.Empty) End Sub ' <summary> ' recursivly get folder items from report ' server catalog; render to treeview control ' </summary> ' <param name="catalogPath"></param> ' <param name="parentNode"></param> Public Sub GetCatalogItems(ByVal catalogPath As String) Dim items As CatalogItem() Try ' if catalog path is empty, use root, if not pass the folder path If catalogPath.Length = 0 Then ' no recursion (false) items = rs.ListChildren("/", False) Else ' no recursion (false) items = rs.ListChildren(catalogPath, False) End If ' iterate through catalog items and populate treeview control For Each item As CatalogItem In items ' if folder is hidden, skip it If item.Hidden <> True Then ' ensure only folders are rendered If item.Type.Equals(ItemTypeEnum.DataSource) Then 'use the GetDataSourceContents to get the definition of the data source. Dim definition As DataSourceDefinition = Nothing Try definition = rs.GetDataSourceContents(item.Path) Console.WriteLine(item.Name) Console.WriteLine(item.Path) Console.WriteLine("Connection String: {0}", definition.ConnectString) Console.WriteLine("Extension name: {0}", definition.Extension) Console.WriteLine("UserName: {0}", definition.UserName) Catch e As SoapException Console.WriteLine(e.Detail.InnerXml.ToString()) End Try Console.WriteLine(item.Name) Console.WriteLine() ElseIf item.Type.Equals(ItemTypeEnum.Folder) Then ' recurse GetCatalogItems(item.Path) End If End If Next Catch ex As Exception Console.WriteLine(ex.Message) Finally 'Do nothing End Try End Sub To run this I use: rs -i GetPropertiesOfDataSources.rss -s http://<reportserver_name>/reportserver > DataSources_<reportserver_name>.txt But as I mentioned it is not picking up all of the data sources and I don't know why. I also don't know why it's so difficult to find all the data sources from within Report Manager. I'm new to SSRS but was told that prior to SQL 2005, this information was easily available using Management Studio to connect to the report server but now it's buried in the Report Manager and not accessible from one spot. Bad move by Microsoft apparently. You'd think that there would be an easy way to find all of the data sources. We need to migrate the SQL Servers on which reports are running, but we have tons of reports and, historically, little documentation for them so we need to be able to determine where all the data sources are so we know which to update. This is proving extremely difficult. Your script helps but is not comprehensive. Any and all help is appreciated. Michael MacGregor, Senior SQL Server DBA, Carlson Marketing