Datasource Dynamic Connection String Problem 2008

Question

We have several different databases on the same server, with the identical structure, and same reports needed for each.  Consequently I added a parameter called dbName, and created a non shared report datasource with an expression for the connection string. The ConnectionProperties.Connectionstring is set to:

=

"Data Source=servername;Initial Catalog=" + Parameters!dbName.Value

When I deploy the report, it fails with the error:

• Error during processing of the ConnectString expression of data source ‘dsNoCatalog’. (rsDataSourceConnectStringProcessingError)

The dbName parameter works.  It is the only parameter. This is 2008.  What do I need to do to dynamically select the database?

Answer 1

where are you trying to run this report from? from BIDS/Visual Studio or from report server? If you sure you are passing the correct value for the dbName parameter, then check the credential of the datasource.

Answer 2

I am running it from the report server, as it no longer worked in BIDS once I used the expression.

The credentials are working, as I have them set the same as before I changed the connection string.  Nevertheless, I tried all the other credential options to see if that was the problem, and it made no difference.

Also, the string was originally: = "Data Source=servername;Initial Catalog=" & Parameters!dbName.Value

I changed it after someone else suggested that the + worked for him.

What am I doing wrong?

Thanks for your reply.

Answer 3

If you running it in report server, all i can say is how are you passing this dbName. Set default value for this parameter. in data sources properties, make sure you set it at custom data source, data source type: Microsoft SQL Server, and whatever credential to access this datasource. Also to note, click on the security properties of this report, see if you have the right role.

Also, I'm assuming the servername in your connection string will be substitute with the correct name, right?

Answer 4

Thanks, the problem was I was trying to pull the parameter available values from a query.  When I added the values manually it worked.