Changing the data source for reports in SSRS using query
Hi,
I have a test environment where we regularly change the active set of server with another non-active set. i.e. Suppose I have 3 roles and 3 servers in Set A, and 3 servers in Set B. I will be swapping the servers from set A to set B and vice versa twice in a week. The application will be pointing first to set A which set A is active and then to Set B when it will be active after swapping. This is a regular process.
What problem I am facing here is with reports. My reports points to all servers in Set A. Now whenever I do swapping, I have to manually update all the data sources from Report Manager Page. This is a hectic task and many times I do forget it. I want to automatize it. Can anyone help me here?
I have a test environment where we regularly change the active set of server with another non-active set. i.e. Suppose I have 3 roles and 3 servers in Set A, and 3 servers in Set B. I will be swapping the servers from set A to set B and vice versa twice in a week. The application will be pointing first to set A which set A is active and then to Set B when it will be active after swapping. This is a regular process.
What problem I am facing here is with reports. My reports points to all servers in Set A. Now whenever I do swapping, I have to manually update all the data sources from Report Manager Page. This is a hectic task and many times I do forget it. I want to automatize it. Can anyone help me here?
johanntagle
I believe you mean SQL Server, not MySQL. Better click Request Attention and have the zones changed.
Hi,
What SSRS version you're using? you can use a SHARED DATA SOURCE, and use that in building your DATA SETS, and only change the set of servers in the shared connection.
this article will give you the idea..
http://blog.infotoad.com/post/2009/07/02/Creating-a-Shared-Data-Source-in-Reporting-Services-2008.aspx
What SSRS version you're using? you can use a SHARED DATA SOURCE, and use that in building your DATA SETS, and only change the set of servers in the shared connection.
this article will give you the idea..
http://blog.infotoad.com/post/2009/07/02/Creating-a-Shared-Data-Source-in-Reporting-Services-2008.aspx
ASKER
thanks john. It was by mistake. I just raised an attention.
Hushlayer,
I am using sql server 2008. I am using shared data source, but the problem is with every swapping, I don't want the pain to publish the data source. Can I do the same with the query?
Hushlayer,
I am using sql server 2008. I am using shared data source, but the problem is with every swapping, I don't want the pain to publish the data source. Can I do the same with the query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't want duplicate reports to be created as they are around 500+ in number just becuase of difference of data sources. There must be some way around. Do we have all the system object for SSRS documented by microsoft?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks huslayer, but I don't want to do this as I don't want to reveal my servers name and number of servers to my clients. Besides modifying 500+ report is again a tedious task.
Well modifying the 500+ reports is a nightmare, but also you could pull the data for that variable from a table using a case statement to switch connection strings by days.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am still not cleared and doing the same way around.