Powershell foreach SQL Server query randomly collates DataSet

Question

I needed to make a simple housekeeping script checking 14 SQL Server's tables. I ended up using Adam's script from this question.

However when run, it skips posting results for a random amount of servers, and then dumps their queries in one table, then does any remaining servers individually. What have I done wrong? (Since I can't comment on Adam's answer)

I wouldn't mind collating all the data in a table and dump at the end, but then with columns for server name and result, if that is easier to accomplish.

This is my script, example output below;

$year = Get-Date -Format yyyy
$servers = gc 'ServerList.txt'

foreach ($server in $servers) 
{
    try
    {
        $ServerName = "$server"
        $DatabaseName = "DatabaseName"
        $Query = "select count(*) from Alarm_Activations where Activation_Date not like '%$year%'"
        #$ds = $null -Tried adding this to fix the issue

        #Timeout parameters
        $QueryTimeout = 120
        $ConnectionTimeout = 30

        Write-Host ""
        Write-Host ""
        Write-Host "  Checking $server "

        #Action of connecting to the Database and executing the query and returning results if there were any.
        $conn = New-Object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Data Source=$server\sqlexpress;Initial Catalog=DatabaseName;Connect Timeout=30; Integrated security=true;"
        $conn.ConnectionString = $ConnectionString

        $conn.Open()

        $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout = $QueryTimeout

        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        $conn.Close()

        Write-Host "Number of activations older than $year :"
        $ds.Tables
    } 
    catch 
    {
        Write-Error "Something went wrong. Seems you have to do it manually!"
        return
    }
}

Example output

The point of dumping the table seems random, but is usually past half the servers.

Earliest I've seen is Server-5, latest; Server-14;

& CheckAlarmActivation.ps1


  Checking Server-1
Number of activations older than 2019 :



  Checking Server-2
Number of activations older than 2019 :


  Checking Server-3
Number of activations older than 2019 :


  Checking Server-4
Number of activations older than 2019 :


  Checking Server-5
Number of activations older than 2019 :


  Checking Server-6
Number of activations older than 2019 :


  Checking Server-7
Number of activations older than 2019 :


  Checking Server-8
Number of activations older than 2019 :


  Checking Server-9
Number of activations older than 2019 :


  Checking Server-10
Number of activations older than 2019 :
Column1
-------
      0
      0
      0
   3318
   1069
   4375
      8
   9357
     74
   1735


  Checking Server-11
Number of activations older than 2019 :
   7917


  Checking Server-12
Number of activations older than 2019 :
   3583


  Checking Server-13
Number of activations older than 2019 :
   5622


  Checking Server-14
Number of activations older than 2019 :
   4166

Answer 1

Like AdminOfThings commented, use Write-Output instead of Write-Host. Because you are using the Write-Host stream and just outputting a variable (which goes to the output stream)($ds.Tables) you are not seeing both streams on the console at the same time.

In addition to using Write-Output, I would approach the problem by storing the results of your DB calls (and any results you want to return from them) as PSObjects that you put into a collection, and then after you iterate through all your severs you can format the output (to console, generate a csv/html report, etc) all in one go. The benefit to this approach is that your script will not fail if one of the servers is down.

Edit I updated the catch block to grab the exception the right way. I also updated where you get the value from the data set to expand out so you do not have a data row object. You can use more advanced Select-Object logic if you have multiple columns, or you want to create calculated properties based on some of your columns. SO post with more info. In your case, you only want to get the count of records back.

$serverResults = @()
$servers = "srv1","srv2"

foreach ($server in $servers) 
{
  # Create an instance of a custom object that will hold the results of your query. Add properties as needed, they can be other complex objects, or simple types. 
   $serverResultInstance = New-Object PSObject -Property @{
   ServerName = $server
   DatabaseName = $DatabaseName 
   Tables = $null
   Exception = $null
   }
 try{
    #Connect to DB like you do (Don't use Write-Host)
    #Add your dataset for this instance 
    $ServerName = "$server"
        $DatabaseName = "dbName"
        $Query = "select count(*) from sys.columns"
        #$ds = $null -Tried adding this to fix the issue

        #Timeout parameters
        $QueryTimeout = 120
        $ConnectionTimeout = 30

        Write-Host ""
        Write-Host ""
        Write-Host "Checking $server"

        #Action of connecting to the Database and executing the query and returning results if there were any.
        $conn = New-Object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Data Source=$server;Initial Catalog=$DatabaseName;Connect Timeout=$ConnectionTimeout; Integrated security=true;"
        $conn.ConnectionString = $ConnectionString

        $conn.Open()

        $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout = $QueryTimeout

        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)

        # Instead of keeping the whole table/row object, expand out the column you want. If you have multiple columns or tables in your dataset you would need to change these values. 
    $serverResultInstance.Tables = $ds.Tables[0] | select -ExpandProperty Column1
 }
 catch{
    #This should have been $_.Exception
    $serverResultInstance.Exception = $_.Exception
 }
 finally{
    $conn.Close()
    $serverResults  += $serverResultInstance
 }
}
foreach($result in $serverResultInstance){
# Format your output here. Make a csv, send an email, etc. Or just:
 Write-Output "Number of activations older than $year :"
 Write-Output $result.Tables

}