SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Connection Strings in Reporting Services 2005


Dynamic Connection Strings in Reporting Services 2005

Author
Message
Bilal Khawaja
Bilal Khawaja
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 31
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/bKhawaja/2945.asp
Steve Giergiel-389916
Steve Giergiel-389916
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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?
Bilal Khawaja
Bilal Khawaja
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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


Andrea K.
Andrea K.
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 67
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
Andrea K.
Andrea K.
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 67
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
Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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?


Debby
Debby
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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?


Debby
Debby
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.


Rosey Keen
Rosey Keen
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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
Rosey Keen
Rosey Keen
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search