Looping 18 servers - need to know how

(1) Share

Report

Posted on by Hopeisgood

Hi.

I am trying to loop through 18 servers to get the data into one excel spreadsheet/tab. I do have a code for every single server that are the same yet in the where clause, the environmentid changes to specific code for each server. I cannot use the UNION ALL in code as not allowed per company's server admins :/

from [planintegration].[dbo].[processlogheader] (NOLOCK) where processlogtypeid like 'AutoQ%'
and createdate >= getdate()-30
and [environmentid] = 4 (the code is different in each server)

You can see 2 different open SQL connections here.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

fatiu 83 on at

Like (0)

Report

Hi @Hopeisgood

Firstly, you can easily handle this by simply creating a variable with array of environment Ids and DB connection, see below.

Then iterate through the array and run the query for each id by dynamically parsing the current item into the Db connection details and in the where clause, see below

This will run through all your dbs dynamically without having to repeat codes.

Was this reply helpful? Yes No
Hopeisgood 27 on at

Like (0)

Report

@fatiu

Thank you for your replies. However, I am very confused. The snapshots did not indicate where I need to log into 18 different servers....

The database is the same for all servers.

Thank you,

Holly

Was this reply helpful? Yes No
Suggested answer

fatiu 83 on at

Like (0)

Report

Hi @Hopeisgood,

This is what I believe you're trying to achieve is you have 18 databases and you want to pull data from each DB

So I suggested that you:

Initialize a variable that contains array 18 dynamic item that you might need for each DB (Like the parameters you need to specify on the where clause of your SQL Query).
Also, Initialize a table that holds all of your data (maybe an excel instance)

You then iterate through each item,

within each iteration:~
You connect your DB
Run your SQL Query (Data is returned)
Write the data to your initialized data source
Close the DB

Do the same for each iteration

Then you'll be left with a data table that holds all of the data and you can do whatever you intend with it.

Was this reply helpful? Yes No