Loop through an array of databases to run a SQL query against database

Question

I am trying to run the same SQL query against a number of databases and output the result to a separate text file for each database. What I'm doing so far has issues with the output file name. When I ammend filename to just use C:\TEMP\usersoutput_$Num.txt, it puts everything in to just one file. Here is my code:

$DBArray = @("DataBase1","DataBase2","DataBase3","DataBase4","DataBase5")
$Num = 0

foreach ($element in $DBArray) {
    (Invoke-Sqlcmd -Query "...." -ServerInstance Server2 -Database $DBArray[$Num] -Verbose
    ) *>> C:\TEMP\usersoutput_$DBArray[$Num].txt |
    foreach {$Num = $Num + 1}
}

Where am I going wrong with this; is this the best way to accomplish this?

marsze · Accepted Answer · 2019-03-12 10:45:35Z

2

For PowerShell to properly parse anything more complex than a variable name inside a string, put the expression in $():

C:\TEMP\usersoutput_$($DBArray[$Num]).txt

But since you are already using a foreach loop, you don't need the $Num index variable:

foreach ($db in $DBArray) {
    Invoke-Sqlcmd -Query "...." -ServerInstance "Server2" -Database $db -Verbose |
    Out-File "C:\TEMP\usersoutput_$db.txt"
}

or:

"DataBase1","DataBase2","DataBase3","DataBase4","DataBase5" | foreach {
    Invoke-Sqlcmd -Query "...." -ServerInstance "Server2" -Database $_ -Verbose |
    Out-File "C:\TEMP\usersoutput_$_.txt"
}

answered Mar 12, 2019 at 10:45

marsze

17.5k6 gold badges59 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ricky Over a year ago

I knew it was a lot more simple, working for me now :-)

Vivek Kumar Singh · Accepted Answer · 2019-03-12 10:47:06Z

2

When you are already using a foreach loop, you do not need to use an increment counter.
If you are using a foreach loop, you need to access the elements inside the loop using the variable $element and not $DBArray.
Make use of $($element) to evaluate the expression inside the variable.

Below will work for you -

$DBArray = @("DataBase1","DataBase2","DataBase3","DataBase4","DataBase5")
foreach ($element in $DBArray)
{
    (Invoke-Sqlcmd -Query "...." -ServerInstance Server2 -Database $element -Verbose) *>> C:\TEMP\usersoutput_$($element).txt
}

answered Mar 12, 2019 at 10:47

Vivek Kumar Singh

3,3601 gold badge19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Loop through an array of databases to run a SQL query against database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related