Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Dynamic Connection Strings in Reporting Services 2005 Expand / Collapse
Author
Message
Posted Saturday, March 31, 2007 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/bKhawaja/2945.asp
Post #355200
Posted Thursday, May 03, 2007 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 5:13 AM
Points: 4, Visits: 12
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?
Post #362908
Posted Thursday, May 03, 2007 10:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

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

Post #363220
Posted Monday, May 07, 2007 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:35 AM
Points: 3, Visits: 53
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
Post #363603
Posted Monday, May 07, 2007 10:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:35 AM
Points: 3, Visits: 53
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
Post #363735
Posted Tuesday, May 08, 2007 5:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

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?

Post #363934
Posted Tuesday, May 08, 2007 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 03, 2010 11:32 AM
Points: 5, Visits: 4

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?

Post #364143
Posted Tuesday, May 08, 2007 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 03, 2010 11:32 AM
Points: 5, Visits: 4

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.

Post #364160
Posted Wednesday, May 09, 2007 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2007 2:54 AM
Points: 6, Visits: 1

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
Post #364449
Posted Wednesday, May 09, 2007 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2007 2:54 AM
Points: 6, Visits: 1

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
Post #364455
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse