Powershell Script to SQL Server database

Question

I have this PowerShell script that will collect the system information of my remote servers.

My problem: I am not sure how to have this script when running to populate my newly created database Hal0Test that I created in SQL Server Management Studio.

End Goal: I would like my powershell script to look at the column in the remote server QAUTILITYDB01 > ServerList (database) > labeled "ServerName" take "QACGAPPSVR01, QACGAPPSVR03, and etc." Push each of those values into my powershell script and generate for each server its FQDN and OS. Lastly, push that new values (FQDN and OS) back to the remote database.

NEW Powershell Script Code:

$connectionString = "Server=QAUTILITYDB01;Database=Hal0Test;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()

$ServerArray = [System.Collections.ArrayList]@()
$query = "SELECT ServerName FROM ServerList"
$command.CommandText = $query
$ServerNames = $command.ExecuteReader()

While ($ServerNames.read()){
    $ServerArray.Add($ServerNames[0])
}

foreach($Server in $ServerArray){
    # $Server returns each server name
    $os    = Get-WmiObject -Class Win32_OperatingSystem -Computer $_
    $disks = Get-WmiObject -Class Win32_LogicalDisk -Computer $_ |
             Where-Object {$_.DriveType -eq 3} |
             ForEach-Object {
                 '{0} {1:D} MB Free/{2:D} MB Used' -f $_.DeviceID,
                     [int]($_.FreeSpace/1MB), [int]($_.Size/1MB)
             }

    New-Object -Type PSCustomObject -Property @{
      'FQDN'            = $_
      'ServerName'      = $os.PSComputerName
      'OperatingSystem' = $os.Caption
      'Disks'           = $disks -join ' | '
    }
    $command.CommandText = "UPDATE ServerList SET FQDN = '$_', OS = '$os.Caption' WHERE ServerName = '$os.PSComputerName';"
    $result = $command.ExecuteNonQuery()

} Export-Csv 'C:\Desktop\HalO\output.csv' -Delimiter '|' -NoType

Powershell CSV file:

ServerName  FQDN    Disks   OperatingSystem
SVR01   Svr01.xxx.com   C: 17899 MB Free/51097 MB Used | E: 22277 MB Free/25597 MB Used Microsoft Windows Server 2008 R2 Enterprise 
SVR03   svr03.xxx.com   C: 18280 MB Free/61337 MB Used | E: 50079 MB Free/56317 MB Used Microsoft Windows Server 2008 R2 Enterprise 
SVR05   svr05.xxx.com   C: 8751 MB Free/40857 MB Used | E: 4987 MB Free/10237 MB Used   Microsoft Windows Server 2008 R2 Enterprise 
SVR06   svr06.xxx.com   C: 14188 MB Free/61337 MB Used | E: 34962 MB Free/56317 MB Used Microsoft Windows Server 2008 R2 Enterprise 
SVR08   Svr08.xxx.com   C: 6464 MB Free/40857 MB Used | E: 5921 MB Free/10237 MB Used   Microsoft Windows Server 2008 R2 Enterprise 

Error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "There is already an open DataReader 
associated with this Command which must be closed first."
At C:\Users\mdaraghmeh\Desktop\HalO\test2.ps1:33 char:5
+     $result = $command.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

SQL ServerList:

Answer 1

Set up your SQL Server connection at the beginning of the script:

$connectionString = "Server=ulidb01;Database=Hal0Test;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()

To execute a query to update the server:

$command.CommandText = "UPDATE ServerList SET FQDN = '$_', OS = '$os.Caption' WHERE ServerName = '$os.PSComputerName';"
$result = $command.ExecuteNonQuery()

OK--now back to the top to get the list of servernames:

$ServerArray = [System.Collections.ArrayList]@()
$query = "SELECT ServerName FROM ServerList"
$command.CommandText = $query
$ServerNames = $command.ExecuteReader()

While ($ServerNames.read()){
    $ServerArray.Add($ServerNames[0])
}

This will give you an arraylist ($ServerArray) of server names. So you iterate through this arraylist in an outer loop:

foreach($Server in $ServerArray){
    # $Server returns each server name
}

...or you could leave it like you have it piped into foreach-object. I just prefer not to use piping in scripts as it is easier to read.

Then at the end of the script you need to close the SQL Server connection:

$connection.Close()