Author Posts

August 29, 2017 at 10:13 pm

I'm working through chapter 8 (Using the Database functions) in Learn PowerShell Toolmaking In A Month Of Lunches. In the code I'm working through there are two modules. One of the modules (MOLDatabase) has the below function (Get-MOLDatabaseData) in it that is then called from another function (Get-MOLComputerNamesFromDatabase) in another module (MOLTools; MOLDatabase module is loaded in the MOLTools module code). It retrieves a list of computer names from a SQL Express database. The information that is returned (3 server names that are in the SQL database, one of which [fake server name: not-online] doesn't really exist) is then passed on to another function (Get-MOLSystemInfo), in the MOLTools module, that gets some WMI information for each server name. Everything works great, except for the fact that a 4th value (The number "3") is returned by the "$adapter.Fill($dataset)" line of code in the Get-MOLDatabaseData function, which is returned by the Get-MOLComputerNamesFromDatabase function, which gets sent to the Get-MOLSystemInfo as if it's a server that the function needs to look up. It fails as follows (i.e. The first warning below should not be there). If I run the Get-MOLComputerNamesFromDatabase function by itself, I get the number three ("3), followed by the table with just the three servers names in the database. How do I suppress the output of the "$adapter.Fill($dataset)" line of code in the Get-MOLDatabaseData function so that it isn't returned?

PS C:\> Get-MOLComputerNamesFromDatabase | Get-MOLSystemInfo

WARNING: Failed to get Win32_OperatingSystem information from 3. To log the error details, re-run the script with the -LogErrors parameter. Get-MOLComputerNamesFromDatabase Get-MOLComputerNamesFromDatabase | Get-MOLSystemInfo

WARNING: Failed to get Win32_OperatingSystem information from 3. To log the error details, re-run the script with the -LogErrors parameter.

ComputerName OSVersion SPVersion BIOSSerial Manufacturer Model
———— ——— ——— ———- ———— —–
OG99SQL 6.3.9600 0 3342-7101-5095-19... Microsoft Corpora... Virtual Machine
OG99SQL 6.3.9600 0 3342-7101-5095-19... Microsoft Corpora... Virtual Machine
WARNING: Failed to get Win32_OperatingSystem information from not-online. To log the error details, re-run the script with the -LogErrors parameter.

PS C:\> Get-MOLComputerNamesFromDatabase

3

ComputerName
————-
localhost
localhost
not-online

–Code from MOLDatabase module file:

function Get-MOLDatabaseData {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName `
            System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName `
            System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($isSQLServer) {
        $adapter = New-Object -TypeName `
        System.Data.SqlClient.SqlDataAdapter $command
    } else {
        $adapter = New-Object -TypeName `
        System.Data.OleDb.OleDbDataAdapter $command
    }
    $dataset = New-Object -TypeName System.Data.DataSet
    $adapter.Fill($dataset) < ----This is the line causing problems.
    $dataset.Tables[0]
    $connection.close()
}

August 29, 2017 at 10:43 pm

Some of my original information got wiped out. The corrected version is listed.

August 29, 2017 at 10:44 pm

I've already looked at https://msdn.microsoft.com/en-us/library/zxkb3c3d(v=vs.110).aspx. The Fill method returns:

Return Value
Type: System.Int32
The number of rows successfully added to or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.

August 29, 2017 at 10:49 pm

FYI, I tried the following. It didn't work.

$adapter.Fill($dataset) | Out-Null

August 29, 2017 at 10:59 pm

I take it back. Sending the fill method out to Out-Null did work. I just had to reload the MOLDatabase module.