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?

Answer 1

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

Answer 2

1. When you are already using a foreach loop, you do not need to use an increment counter.
2. If you are using a foreach loop, you need to access the elements inside the loop using the variable $element and not $DBArray.
3. 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
}