SSRS Dynamic data source is not working

Question

I have a dynamic data source in my SSRS Report. It will get its server name from a parameter defined in the report. I defined my dynamic data source connection string as

="Data Source="+Parameters!HostServer.Value+";Initial Catalog=DBName"

When I am trying to define a data set using this data source, I am not able to extract the fields out of the query used in the data set. The error I am facing is that "Could not update a list of fields for the query. Verify that you can connect to the data source and the query syntax is correct..."

If I use the same query against my server in SSMS editor, I am getting expected output. And the parameter has already a default value which I suppose the report will use while testing connection.

If anyone know how to resolve this connectivity issue with dynamic data sources, please help.

Answer 1

You should create another temporarly datasource where you need to set connection to one of your databases. Then when you define dataset use that temporarly datasource in order to populate the available fields. Once the fields are created, you can change the connection back to your dynamic data source. After that delete temporarly datasource and it all should to work.