Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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?
0
TempDBA
Asked:
TempDBA
3 Solutions
 
johanntagleCommented:
I believe you mean SQL Server, not MySQL.  Better click Request Attention and have the zones changed.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
0
 
TempDBAAuthor Commented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jason Yousef, MSSr. BI DeveloperCommented:
I don't believe there's a way around that, except maybe doing 2 versions of the reports !
0
 
TempDBAAuthor Commented:
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?
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
You could also use a dynamic data source as a parameter and let the users select it, but that will need to edit all the reports!!

example:
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/one-report-for-many-instances-dyanmicall
0
 
TempDBAAuthor Commented:
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.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Please use RS Tool to export report. (Generate SCript for DAtaSource)

Edit the script file by code.

Execute script on same server.

for this create a SSIS package and automate it in SQL Job.
0
 
TempDBAAuthor Commented:
I am still not cleared and doing the same way around.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now