September 9, 2019 at 5:12 pm
Hi,
I need to create a list of data coming from different servers (same DB model but different content). To do so, I have a report that runs a query on a first DB (repository DB) that gives me a list of servers (Clients DB) to get data from. In this report I have then just a subreport that takes as parameter my new server IP to check.
in the subreport I have a dataset that returns x number of records (from client DB). I would like to insert those records in my repository DB (which is a different datasource).
Today I'm justing printing on the screen the result of my query server by server but I can't do any sorting as it is different data sets. The objective is to concatenate the result of each query to 1 table on my repository DB and then do a select from the main report to sort the records correctly.
Don't know if all my explanations are clear but if you have an idea on how to do so, I would be happy to know. (Right now I'm exploring a new subreport in my currentsubreport where I'll pass as parameter one record from client DB and store it in repository DB but it will make a lot of connections to repository DB if it works....)
I'm working with VS 2019 / SQL 2016.
Thanks for reading me.
September 9, 2019 at 10:04 pm
What query are you executing on the different servers? When you get the list of servers do you only get a list of IP addresses or is there usename and password too?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 10, 2019 at 6:02 am
Hi,
I just get the list of IP I need. I use a unique domain login/pwd to connect all databases.
On my main report (tablix of details containing a subreport) I do :
select serverIP from Connections where environment='production'
In my subreport I do below query for each servers of my previous list :
select @ServerIP, job, sum(quantity)
from orders
group by @serverIp, job
Now i'm displaying this list as a table in my subreport but what I would like is to store the records of the second query in a temp table on my 1st database (which is on a different server)
September 10, 2019 at 12:53 pm
It's possible within tsql to set up linked servers to submit queries to different db's. However to switch between the linked servers you must use the word "go". I'm pretty sure there is not any type of loop within tsql that permits the inclusion of the word "go". What I'm about to write changes if that's not the case...
Is the list of IP addresses really variable? Is it variable because some db's have activity and some do not? If there are 20 db's in total and only 3 have activity it seems likely there would be little penalty in querying all 20 db's every time... is that so? Because you could create a script that explicitly references all 20 (or however many) linked servers. If the list is truly variable and you must do a "for each" loop through IP addresses to submit the queries, then a language outside of tsql might be best. C# could be a good choice.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply