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?

score 2 · Accepted Answer · answered Mar 12 '19 at 10:45

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"
}

I knew it was a lot more simple, working for me now :-) – Ricky Mar 12 '19 at 10:57 — Ricky, Mar 12 '19 at 10:57

score 2 · Answer 2 · answered Mar 12 '19 at 10:47

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
}

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

2 Answers2