Dynamic Connection Strings in Reporting Services 2005

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/bKhawaja/2945.asp

  • I am afraid I cannot get this to work as it complains about connection string expressions are not allowed in Shared Data Sources, and does not allow closing the dialog. Was this something that used to work in an earlier version of SSRS I wonder?

  • Hi Steve.

    We are trying to get away from shared data source. You cannot use a shared datasource with this expression based connection string, it defeats the purpose of making it dynamic.

    You pass in the server name and database name in the parameters or in the url to dynamically generate report against the database and server you are sending in. so your expression based connection string replaces the parameter names with actual name of the server and database like this.

    ="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value

    Data Source=(local);initial catalog=Northwind

    Hope i answered your question.

    Bottom line is , You cannot use shared datasource in this case.

     

    Bilal

  • Anyone got it working with multiple data sets? I get this error:

    "Error during processing of the ConnectString expression of datasource ‘xxx’."

    Any hint would be greatly appreciated!

    Andrea

  • Ok, found it. the problem was only partial due to the multiple data sets: Of course, if you have other parameters in the report whose valid values depend on tables in the data base to choose, you have to put the server and db parameters on top of the parameter list ..

    cheers

    Andrea

  • I got real excited when I saw the article as this is something we need, but it is unfortunate that you cannot use it with shared datasources... We only work with shared datasources in SSRS...

    Anyone has any ideas around manipulating connection information for shared datasources?

  • I've just tried this process and it works great for datasets that do not use stored procedure.  When I use a stored procedure, RS cannot validate the procedure when I deploy and changes command type from stored procedure to text.  Once on the server, the report can't execute the stored procedure(s) since they are now text. 

    Suggestions?

  • OK.  I stopped fighting RS and left the command as text and used the syntax

    EXEC storedprocedure @parm1, @parm2

    for the stored procedure.

    Now I need assistance passing the server and database parameter in the URL instead of requiring the user to complete.

  • I still get an error, before I get to enter the server/database details, how does one stop this from happening?

    • An error has occurred during report processing.

      • Cannot create a connection to data source 'ClientDB'.

        • For more information about this error navigate to the report server on the local server machine, or enable remote errors


    Kindest Regards,

    Rosey

  • Sorry, I meant to ask, does this also work for embedded (Jump to) reports and if so, how do I get the embedded report to run using the same server/database parameters?

    Thx,

    Rosey.


    Kindest Regards,

    Rosey

  • Debby,

    I only use Stored procedures and it works perfectly fine with Reporting Services 2005 expression based connection string.

    Any time you edit the dataset, and try to save or hit ok after editing, it changes the commantype to Text from Stored procedure. So you have to manually change it back to Storedprocedure and Save the Report and then preview it or deploy it and it should work.

    Hope it works for you now.

  • Rosey,

    it can definetly work for jump to reports. You just have to pass the serverName and database name to the target report that will be accepting these.

    =Parameters!ServerName.Value

    =Parameteres!DatabaseName.Value

  • Adriaan,

    Dynamic connection string is a Great feature in RS2005. When you use shared datasources, you HARDCODE the Servername and Database name in there which you cannot change.

    What if  you want to run the same report against a different Server and a different database? you have to create another dataset and tie that with the new report and re-deploy and now ure creating multiple reports for each server/database.

    With Dynamic Connection string, You just need one report deployed on a WebServer.

    you can run that report against any database on anyserver by simply passing in the servername and databasename.

    That one report will generate different data each time you run the report against a different database.

    What if you have 5 databases? you can create only one connection string in a dataset and thats hardcoded and cannot be changed when you deploy it. so that report will run against connectionstring/database you hardcoded.

    With Dynamic connection string, nothing is hardCoded.. you simple pass it the Server and database names and it will show you the results.

    Just One report against 100 databases!

     

     

  • Debby,

    You can pass in the ServerName and Database in the URL like this:

    You need to have ReportViewer first and set its ReportServerURL property first:

    http://localhost/ReportServer

    Then do this in your code. you need to put your own server Name and database name and the path.

    ReportViewerS1.ReportPath =

    String.Format("/MyReportsFolder/ReportName&ServerName={0}&DatabaseName={1}", "Dev","Northwind")

    Hope that helps.

    Bilal

  • I do not understand: "You need to have ReportViewer first and set its ReportServerURL property first: http://blockedcontent/ReportServer"

    I am passing the url from an asp page (not .net) is this my problem?  The url format is

     
    This format works to render report w/o database & server variables.
     
    Thank you,
    Debby
     

Viewing 15 posts - 1 through 15 (of 71 total)

You must be logged in to reply to this topic. Login to reply