2

I have a bunch of SSRS 2008 reports which I'd like to run on several different machines (development, test, production). Each machine has it's own database, so I need to use different connection strings depending on where the report is running.

One workaround I found is to specify the server and catalog name in a hidden parameter that is passed to the report at runtime. It's described in this tutorial, but it applies to SSRS 2005 and I could not make it work in SSRS 2008.

Everything works fine when the connection string in my shared datasource looks like this:

Working Connection String

Just so to see if expressions can be used at all for the connection string, I replaced the connection string with this:

Dynamic but broken connection string

But this gives me the following error when I try to preview the report in Visual Studio:

An error occurred during local report processing. The item '/Zeiterfassung-Adrian' cannot be found.

Are dynamic connection strings still working in SSRS 2008?

If yes, what am I doing wrong?

If not, what else can I do?

Improve this question
4
  • Depending on what audience is actually running these reports you might be interested in custom reports in management studio. msdn.microsoft.com/en-us/library/bb153684.aspx Commented Jan 21, 2010 at 3:38
  • While I know this question is a little old, what version of BIDS is the screen shot from? My 2008 BIDS has the little "fx" below the Edit button which allows you to do what you are asking about. Commented Nov 22, 2011 at 21:43
  • @D.S.: It's from Visual Studio 2008 Team System. Commented Nov 23, 2011 at 14:31
  • 1
    @D.S. the fx button is located on the "Data Source Properties" window but not on the "Shared Data Source Properties" window. So only embedded data sources can be made dynamic using expressions. Commented Jan 9, 2013 at 7:35

6 Answers 6

Reset to default
2

It's because it's a shared datasource. Should work fine for a regular embedded datasource. Take a look at THIS link for an option on using dynamic connections strings with shared datasources, might be helpful for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Just create your report datasource for each environment and deploy them. Then switch your deploy option to not overwrite a datasource. Though to make for simple deployments you will have to configure the configuration manager in bids for each environment. This is how we work in our multiple environments.

Hope it helps, let me know if you have any questions on this.

Improve this answer

1 Comment

Thanks for your answer! It's not perfect, but it seems to be the only working solution under the given circumstances.
1

Please check related article at http://haseebmukhtar.wordpress.com/2011/11/09/dynamic-database-in-ssrs-2008/

Also you can not use dynamic database settings for the shared data source.

Improve this answer

Comments

0

The string should have double double quotes for server name. ="data source="" & Parameters!MyServerParameter.Value & "";initial catalog=DBName.."

Improve this answer

1 Comment

Hmmm... I just tried it, but it did not work. I even tried ="Data Source=88.198.25.106;Initial Catalog=Zeiterfassung-Production;Persist Security Info=True" or =""Data Source=88.198.25.106;Initial Catalog=Zeiterfassung-Production;Persist Security Info=True"" just to see if expressions are supported at all, and not even that would work properly.
0

I was able to create a dynamic embedded connection using a ServerName parameter as follows:

="data source=" & Parameters!ServerName.Value & ";initial catalog=master"

Improve this answer

Comments

-1

What about using a hidden report parameter?

then you should be able to do:

="data source=" & Parameters!MyServerParameter.Value & ";initial catalog=DBName.."

Here is an article which should help you out: http://msdn.microsoft.com/en-us/library/ms156450.aspx

Improve this answer

2 Comments

You are suggesting exactly what is already shown in the second screenshot of my opening post. Thanks for the link though, but at least now I know that this SHOULD work, but I still don't know why it doesn't.
Yes that should work, one thing you can try is to hardcode the entire connection string. Then add a text box to your report and reference the parameters I have shown. Run the report and see if you are getting a value in that textbox.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.