InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 15:14:07 GMT20http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxMicrosoft's solution to this problem is the introduction of "Expression-based Connection Strings" (see [url=http://msdn.microsoft.com/en-us/library/ms156450.aspx#Expressions]this article[/url] for reference) which unfortunately does not apply to shared data sources.Fortunately, by using a tool, some report design standards, and a Report Definition Customization Extension (RDCE), it is possible to change the Data Source References on the RDL file to point to the right Server-Database pair.You can check this article:[url=http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL]Dynamically Pointing to Shared Data Sources on SQL Reporting Services using a Report Definition Customization Extension (RDCE)[/url]Thu, 05 Apr 2012 18:54:19 GMTcarlos5686http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxok.step 1: create one simple report for one database and run it.(check whatever you want is there or not)step 2: then create one parameter 'SelectDatabase'. now go to parameter properties-> available values-> specify values->;(add your databases name here[how many you want])step 3: go to datasource properties and paste this ="Data Source=ServerName;Initial Catalog="+Parameters!SelectDatabase.ValueStep4: Save and Run your report . Now you have to select database. Select it and run report.Let me know if you have some problem.ThanksMon, 28 Nov 2011 09:31:45 GMTRitesh_Devhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHi Grasshopper,All db's are on one server and I am only looking to access one of them at a time.Mon, 28 Nov 2011 09:00:08 GMTjvansandthttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI have few questions:1. Are all databases in one server?2. Do you want to select one database at a time or multiple?ThanksMon, 28 Nov 2011 08:57:10 GMTRitesh_Devhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI am trying to statically set the Server and choose which database that I want to pull the reports from. Is that possible? And if so, is it possible to choose which db's that I want to use and restrict the list to those?Mon, 28 Nov 2011 06:57:35 GMTjvansandthttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxFirst things first. If you want to use Server Names in the drop down list then make a dataset which would populate your server names. and then create another dataset with servername as parameter and populate database names. Now your Stored Procedure should be written using execute sql command SP_executesql N'servername+databasename+storedproc'so now you will get the dropdown list populated which inturn would execute your stored proc on different servers. remember if you have a query instead of SP then you need to have dynamic sql statements prepared.let me know if you have any more questionsFri, 25 Nov 2011 08:04:27 GMTcrunchycocktailhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHI Jeswanth,Thanks for the reply!! Sorry to say but your method won't work. You are just making server name dynamic.My question was can we select multiple values for parameter with that connection string.As it is single connection string and it can take only one database at a time. And in your given string it can take only one server name and related database name.I hope you understand now. Some1 has any idea about selecting multiple values for parameter. Please let me know.ThanksThu, 24 Nov 2011 06:46:16 GMTRitesh_Devhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspx[quote][b]ankurk2 (11/23/2011)[/b][hr]Anyone got it working with multiple values of parameter? I get this error:"Error during processing of the ConnectString expression of datasource ‘xxx’."conn string: ="Data Source=206.17.24.23;Initial Catalog="+Parameters!CountryName.ValueFor single value its working fine but foe multiple values its not working.Any hint would be greatly appreciated!Ankur[/quote]Ankur,you may use the following string for creating conneciton string="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DBName.Valuewe use Windows Authentication to connect , so the first parameter is for servername and second for databasenamethis works.JeswanthWed, 23 Nov 2011 21:18:04 GMTJeswanth Jaishankerhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxAnyone got it working with multiple values of parameter? I get this error:"Error during processing of the ConnectString expression of datasource ‘xxx’."conn string: ="Data Source=servername;Initial Catalog="+Parameters!CountryName.ValueFor single value its working fine but foe multiple values its not working.Any hint would be greatly appreciated!AnkurWed, 23 Nov 2011 09:33:56 GMTRitesh_Devhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHey Crunchy...what did you do to get the database names into a dropdown? That is the only part that I am trying to make work.Wed, 16 Nov 2011 09:24:05 GMTjvansandthttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI found your article interesting and a great solution. I am testing it out and receive this error and cannot seem to implement a work-around. An error has occurred during report processing.Cannot create a connection to data source 'master'.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Do you have any idea what is causing this error? I already checked the remote connectios and we do allow them against the test servers.Mon, 03 May 2010 12:17:17 GMTsamcnealhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxyes you can , but all teh sqls in data set shluld be stored procs. then u can pass username and password as a parameters to the report.Wed, 07 Apr 2010 08:53:40 GMTJeswanth Jaishankerhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxAlso, what if we don't know the credentials. can we dynamically supply the user and its password to the datasource?Tue, 06 Apr 2010 10:12:55 GMTArshad Kalamhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxIf you are using Active Directory (AD) user accounts then this is easy, you just have the datasource use Windows Integrated Security. If you are not using AD then I suspose you could probably create some code to map the users AD account to a SQL Server account and have it logon automagically. Otherwise you could use "Credentials Stored Securely on the server" where you use a single user name and password for the data source and control access to the reports by user using the security tab under each report or folder.Tue, 06 Apr 2010 07:19:49 GMTRobert Haackhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxhow about username and password are there any methods for dynamically allocating them. i am aksing you this in the context of having multiple datasets. i have 6 to 7 datasets each having same login id and password. so if i can use parameters for the username and password then it would be help full but 205 does not allow me to do thatMon, 05 Apr 2010 20:02:04 GMTcrunchycocktailhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxThere is no table is SQL Server that will give you this information unless you want to create and maintain a table with this information. Otherwise you need to look at some sort of query outside of SQL Server to get this information, like using AD to query all the computers in the domain, but even with that unless your computer names have a unique id to tell you that it's a SQL Server you are stuck. Jeswanth may have the better idea for you but not having ever used that method I can't validate it.Mon, 05 Apr 2010 11:24:54 GMTRobert Haackhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxi am trying work on a report with dynamic connection strings in ssrs2005. i was able to generate the list of the databases to use for the initial catalog= Databasename but i would like to know if we can generate the list of the servers using a query so that we can select the list of the servers. also i have a question with the username and password. when i try to give them dynamically it is not working for me. any guess.my main aim is to get the name of the server as available values for the data source = parameters!servername.value Mon, 05 Apr 2010 11:18:02 GMTcrunchycocktailhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHi,not sure where u want to load teh list of sql servers in a list box, if its supports .net language u can ue sql serevr smo and there are classes which will list the sql servers in LANJeswanthMon, 05 Apr 2010 01:22:27 GMTJeswanth Jaishankerhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxA couple questions:1) Are you using Active directory?2) Are you servers named in such a way that you can filter out which one's are SQL Servers and which ones are not?Thu, 01 Apr 2010 14:44:46 GMTRobert Haackhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxok i have been able to get the database names by changing the connection string for the specific dataset but i would also like to get the dropdown list for the server names on the network. any ideaThu, 01 Apr 2010 14:08:13 GMTcrunchycocktailhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI believe you could connect to Master as the default database, run your query to get the list of databases to populate the drop down.Thu, 01 Apr 2010 11:32:14 GMTRobert Haackhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxi have to use the dynamic connection string as it was shown in here and i have to get a list of database values if the user selects a particular server name and i tried to use exec sp_databases and in order to populate this data set i need to pass the connection string and the connection string does not have the database name in it so how can i get the list of the database names and give them as available values for the initial catalog parameter. i think you get my point but i really need to get this working. also i am concerned about how are we going to work with reports that share a same datasource. do we have to pass in the satasource for each report individually or is there a way out of that. any help would be apperciated. thanksThu, 01 Apr 2010 10:56:31 GMTcrunchycocktailhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxthx Debby, You saved lot of time for me. even i was fighting with rs and MSDN for this :)Tue, 29 Dec 2009 00:41:39 GMTJeswanth Jaishankerhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHi,I did everything stated in the instructions but I am getting this error:An error has occurred during report processing.Cannot create a connection to data source 'TEST'.For more information about this error navigate to the report server on the local server machine, or enable remote errors Can anyone help I'm just a newbie in RS.Thanks!PoyFri, 18 Sep 2009 15:01:13 GMTquasar_phoenicishttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxThanks for a very good article but whether this will support shared data source. I'm getting error.Mon, 20 Jul 2009 01:32:02 GMTArunkumar S Phttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxthankslet me know if you find a work around that oneThu, 18 Jun 2009 08:12:07 GMTNomvulahttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHi Nomvula....Sorry bro...I tried a lot but I didnt get what I exactly wanted......So that task is still pending in my worklist..........Thu, 18 Jun 2009 05:13:49 GMTgrkanth81http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxhi grkanth81did you get a the work around with this issue? i also have a similar situation where i have to develop a report which will display data from different servers and different database, all databases uses similar tables, views and storedproc and on the report should display grouped by each database name and i don't have to enter the server name as parameter cause i want to display everythingThu, 18 Jun 2009 00:51:48 GMTNomvulahttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI'm having problems with this same issue under 2008. If somebody has this working can you share what the datasource looks like from Report Manager perspective?Here is what I see in the log file:library!ReportServer_0-18!1200!04/08/2009-09:37:58:: Call to GetPermissionsAction(/SYSPRO/Customer (Dynamic Data Base)).library!ReportServer_0-18!1200!04/08/2009-09:37:58:: Call to GetSystemPropertiesAction().library!ReportServer_0-18!1224!04/08/2009-09:37:58:: Call to GetPropertiesAction(/SYSPRO/Customer (Dynamic Data Base), PathBased).library!ReportServer_0-18!1200!04/08/2009-09:37:58:: Call to GetSystemPermissionsAction().library!ReportServer_0-18!1200!04/08/2009-09:37:58:: Call to GetPropertiesAction(/SYSPRO/Customer (Dynamic Data Base), PathBased).library!ReportServer_0-18!1200!04/08/2009-09:37:58:: Call to GetSystemPropertiesAction().library!ReportServer_0-18!5f4!04/08/2009-09:37:58:: i INFO: RenderForNewSession('/SYSPRO/Customer (Dynamic Data Base)')processing!ReportServer_0-18!5f4!04/08/2009-09:37:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'. at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) at Microsoft.ReportingServices.DataExtensions.ConnectionExtension.Open() at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open() at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenDataExtensionConnection(DataSource dataSourceObj, OnDemandProcessingContext pc, CreateDataExtensionInstance createDataExtensionInstance) --- End of inner exception stack trace ---processing!ReportServer_0-18!5f4!04/08/2009-09:37:58:: e ERROR: Data source 'DataSource1': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'.processing!ReportServer_0-18!5f4!04/08/2009-09:37:58:: e ERROR: An exception has occurred in data source 'DataSource1'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'.processing!ReportServer_0-18!5f4!04/08/2009-09:37:58:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources ...processing!ReportServer_0-18!5f4!04/08/2009-09:37:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ; Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'. --- End of inner exception stack trace ---chunks!ReportServer_0-18!5f4!04/08/2009-09:37:58:: w WARN: Rolling back shared chunk transaction for snapshot '967c1063-ee98-4098-9d44-8fa387ef10d5', Permanent=False.library!ReportServer_0-18!5f4!04/08/2009-09:37:58:: w WARN: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'.webserver!ReportServer_0-18!5f4!04/08/2009-09:37:58:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.ArgumentException: Keyword not supported: '"data source'.Thanks,Robert HaackWed, 08 Apr 2009 10:26:54 GMTRobert Haackhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxThanks for this post. It is EXACTLY what I need. But does anyone know if this works in SRS 2008? I am getting several errors depending on how I format my string, but none of them work so far. My current error is:An error has occurred during report processing. (rsProcessingAborted)Cannot create a connection to data source 'DataSource2'. (rsErrorOpeningConnection)Format of the initialization string does not conform to specification starting at index 32. Here is the connection string I am using for datasource:Data Source=server\instance ";initial catalog=man_"&Parameters!Database.Value Our database names are all xyz_***. I only want the user to have to enter the *** part of the db name not the xyz_ part; the first 3 letters; because they won't know it.TIA!!SunnyDMon, 06 Apr 2009 16:13:42 GMTSunnyD-1048338http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHi Bilal,The article which you have posted on Dynamic Datasource has helped me in getting a good idea on how to do it..........I have done exactly what you have mentioned in the article......and after deploying, when I am viewing the report,its asking me to enter the server name and the database name....after entering the values, the report is generated......Is it possible to do this way:Lets say I am having 5-6 servers on my SQL SERVER and then there are around 7-8 databases under each server.....SO I want to see the report in dropdown values of server and database rather than entering values manuallyFor the server, I will just assign 5 Non-Queried values in the report parameters window......and now what I want is, when I select any value from server dropdown box, I need to get the respective databases present on that server.....Is this possible???If so Can you give any suggestions of How to do it????Thanks,Wed, 17 Dec 2008 20:26:15 GMTgrkanth81http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHello Swetha, I got this working. I am database credential as the report parameters. Sorry did not post my reply on this. What i did was.When you are creating the report i followed these steps1. Create the database connectionstring with the hardcoded value.(Data Source=1.1.1.10;Initial Catalog=Mydatabasename). Finish the query writing. So that you can test query returns right rows.2. Build and deploy with this connectionstring.3. Then change the connectionstring with the parameters value something like this(="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!ClientName.Value)4. Just deploy it.5. Your report should work fine.And also add all the permissions necessary to access that server. ASP.net, IIS, database level permissions.I followed these steps and my report works fine now.Sorry for replying, But thanks for the ideas i got from this thread and the forum.ThanksWed, 12 Nov 2008 07:40:33 GMTfreesmile50http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxWe have the smae issue. I dont pass the database name and credential in the URL but I do pass dynamic parameter values in it. The deploying is successful. The error message being received is :• An error has occurred during report processing. (rsProcessingAborted) o Cannot create a connection to data source 'SQLDatabase'. (rsErrorOpeningConnection) § For more information about this error navigate to the report server on the local server machine, or enable remote errors If anyone has found a workaround for this please let us know.Thanks.Wed, 12 Nov 2008 07:17:04 GMTSwetha Sankaran-465186http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxHello guys, I am actaully in similar situation. I have multiple database to chose from. But my situation is , i will be passing my database name in the querystring of the report url. Because i already know which database the report has to look into. I tried steps given still i am getting the following errorAn error has occurred during report processing. (rsProcessingAborted)Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)For more information about this error navigate to the report server on the local server machine, or enable remote errors Can anybody help me on this. I need this done badly and urgently. Thanks for help in advanceWed, 08 Oct 2008 12:55:19 GMTfreesmile50http://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxI am trying to show the reports to the users in the report viewer. I am passing the server name, database name, userid and password as parameters to the report viewer.In the report project I can pass them as parameters and it works perfectly, however when I get the report viewer involved I get the following message:[quote]The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting) [/quote]Does anyone have any ideas?Wed, 25 Jun 2008 06:14:21 GMTjohn.mohrhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxThanks for your help. I finally got it to work by selecting "the credential supplied by the user" and checking "Use as windows credentials when connecting to the data source check box." Thanks again. Happy Friday.Fri, 02 May 2008 12:17:53 GMTHuonglien Nguyenhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxIt shouldn't be the dynamic data source. I have 5 reports with a dynamic data source deployed on two different server with no problems (so far).Fri, 02 May 2008 09:50:09 GMTEllen Heijmanshttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxEventhough I got the message saying "deployed successfully" there is no data source stored on the report server. I suspected may be because of the dynamic connection string or some kind of security problem here because I had no problem to deploy other reports. Of course they all have shared data source not a dynamic one.Fri, 02 May 2008 08:56:56 GMTHuonglien Nguyenhttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxDid you, on the Report Server, go to the properties of the report, Data Sources. On the section Connect Using I gave the credentials for Credentials stored securely in the report serverFri, 02 May 2008 08:11:51 GMTEllen Heijmanshttp://www.sqlservercentral.com/Forums/Topic355200-379-1.aspxThanks. But it is the only parameter that I have in the list. Of course I try to build a dynamic connection to all servers and everything works fine under my Visual Studio 2005 environment but I had no luck after deploying it to the report server.Fri, 02 May 2008 07:59:34 GMTHuonglien Nguyen