Convert sql Collection to String in Powershell

Question

I've been trying to pull data from a sql query and get it converted to HTML to finally embed the results in an email body.

My code is as follows;

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand 
$SQLCommand.CommandText = "SELECT DISTINCT SYS.Name,LDISK.DeviceID0,LDISK.Size0 AS DiskSizeMB,LDISK.FreeSpace0 AS FreeSpaceMB,SCCM.dbo.v_GS_WORKSTATION_STATUS.LastHWScan,SCCM.dbo.v_GS_LastSoftwareScan.LastScanDate
    FROM v_FullCollectionMembership_Valid SYS
      JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
      INNER JOIN SCCM.dbo.v_GS_WORKSTATION_STATUS
        ON LDISK.ResourceID = SCCM.dbo.v_GS_WORKSTATION_STATUS.ResourceID
      INNER JOIN SCCM.dbo.v_GS_LastSoftwareScan
        ON SCCM.dbo.v_GS_LastSoftwareScan.ResourceID =
        SCCM.dbo.v_GS_WORKSTATION_STATUS.ResourceID
    WHERE
      LDISK.DeviceID0 = 'C:' AND
      LDISK.DriveType0 = 3 AND
      ((LDISK.FreeSpace0 <= ((LDISK.Size0 * 10) / 100)) OR
        (LDISK.FreeSpace0 <= 1024)) AND
      SYS.CollectionID = 'SMS00001'
    ORDER BY
      SYS.Name"
    $SQLCommand.Connection = $SQLConnection 

    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SQLCommand                  
    $SQLDataset = New-Object System.Data.DataSet 
    $SqlAdapter.fill($SQLDataset) | out-null 

    $FileInfo = $SQLDataset.tables | FT -AutoSize

The resulting format of the data stored in $FileInfo looks good;

Name DeviceID0 DiskSizeMB FreeSpaceMB LastHWScan LastScanDate
---- --------- ---------- ----------- ---------- ------------
Server01 C: 53244 2010 7/28/2017 3:18:01 PM 7/28/2017 5:25:51 AM

...however when I pipe this to ConvertTo-HTML ($FileInfo | ConvertTo-HTML) the resulting format comes out like this;

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/><col/><col/><col/></colgroup>
<tr><th>ClassId2e4f51ef21dd47e99d3c952918aff9cd</th><th>pageHeaderEntry</th><th>pageFooterEntry</th><th>autosizeInfo</th><th>shapeInfo</th><th>groupingEntry</th></tr>
<tr><td>033ecb2bc07a4d43b5ef94ed5a35d280</td><td></td><td></td><td>Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo</td><td>Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo</td><td></td></tr>
<tr><td>9e210fe47d09416682b841769c78b8a3</td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>27c87ef9bbda4f709f6b4002fa4af63c</td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>27c87ef9bbda4f709f6b4002fa4af63c</td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>27c87ef9bbda4f709f6b4002fa4af63c</td><td></td><td></td><td></td><td></td><td></td></tr>

When I look at the type of my $FileInfo I get this;

$FileInfo.GetType();

IsPublic IsSerial Name                                     BaseType                                                                                                                                                               
-------- -------- ----                                     --------                                                                                                                                                               
True     True     Object[]                                 System.Array

...so I suspect that the ConvertTo-HTML module is looking for input in string format however I can't seem to get this to work properly - even after trying options like $FileInfo | Out-String -Stream

I want to believe that this can be done easily - I just can't find the right approach. Thanks in advance!

Generally, PowerShell cmdlets expect (data) objects as input (not strings), and ConvertTo-Html is no exception. Formatting cmdlets such as Format-Table (ft) should solely be used for immediate display - the output of such cmdlets is not useful for processing the data further, so assigning Format-Table output to the variable you're piping to ConvertTo-Html will not produce meaningful results. — mklement0
– mklement0, Commented Jul 29, 2017 at 4:08
Thanks mklement0 - I'll have to remember that about Format-Table — mandg
– mandg, Commented Jul 31, 2017 at 12:57

Esperento57 · Accepted Answer · 2017-07-31 08:53:48Z

2

Remove format-table, use select with exlude not necessary option with first table, try this

$SQLDataset.tables[0] |
    select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | 
        ConvertTo-Html

edited Jul 31, 2017 at 8:53

answered Jul 30, 2017 at 17:46

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mandg Over a year ago

That's perfect - thanks Esperento57! Now it displays exactly like I've been trying for days!

Collectives™ on Stack Overflow

Convert sql Collection to String in Powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related