Click here to monitor SSC
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
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

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.


Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

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


Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

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!


Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

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


Debby
Debby
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4

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

Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

If you don't wanna embed Reports within the ReportViewer in a WebForm then you don't need to use the ReportViewer.

This format should work. https://RSServer/Reportserver?/ReportFolder/ReportName&rs:Command=Render&server=ServerName&database=DataBaseName

as long as your Report has ServerName and Database name parameters defined.

Bilal


Adriaan Davel
Adriaan Davel
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 13

Hi Grasshopper,

Thanks for the reply. The reason why I don't use Custom Datasources is because of the security configuration required. We use shared datasources executing on a specific SQL login. If we need to change the password of the reports login, we only do it in the shared datasources.

We have often found that knowledgeable users exploit Windows Authentication on SQL server, which is another reason why I don't want to pass the Server Name and Database Name in a URL, it opens up the security even wider.

I was hoping that there is a way that I can manipulate the datasource through a class library, and have the report extract the data from a specified database (and Server), without having to pass security information in URLs...


Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

Adriaan,

If we are concerned about Security, you can just hard code the username and password in the DataSource Credentials Tab.

As far as DatabaseName and ServerNamer are concerned, you don't have to pass in the full database ans servername in the url.

if your ServerName XYZDallas and DatabaseName is ABCDB

just pass in "Dallas" and "DB" and hard cord their prefixes in the connection string. so the user would not know what the actual database name is...

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

If you want to maniplulate through a class library, you still are hardcoding server and database information in there. you will not have the luxury to pass in whatever u want.


Adriaan Davel
Adriaan Davel
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 13

Hi Grasshopper,

I'm not shure how hardcoding the UserName and Password in the DataSource tab would improve the security, please explain.

If you have a large number of reports, and the username and password that you use on the reports leaks out, you will have to change it for every report, making it a nearly impossible task, where as if you use shared datasources, you would only change a few datasources. This in my opinion is a major security risk.

I am also very conservative with using patterns in database names, we typically try to stay away from it as it can become a limitation if your application scales out drastically...


Bilal Khawaja
Bilal Khawaja
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 31

being on your secure firewalled intranet is ok.

out in public on Internet, i don't want to send those in..

But my main concern is the flexibility on having 1 report run against million server/databases as long as u have access to those servers from where the report is deployed.


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