CODEWITHSUNDEEP
htmlsqlsql-serverpowershell
ch41n54w
ch41n54w

Reputation: 13

Parameter -ExcludeProperty disables table name from SQL

I am creating an HTML report with PowerShell which displays several SQL queries. Now I have the problem, that -ExcludeProperty from Select-Object disables given table names of SQL queries.

At the moment I am excluding this: 'RowError, RowState, ItemArray, HasErrors', but I need to exclude 'Table' too. But if I am excluding it, given names will be replaced by '*'.

This query:

SELECT count(*) AS 'Files in queue' FROM ...

is for example displayed this way:

 ---------
|____*____|
|______37_|

Can you help?

Edit: Thanks for your feedback! Double quote is not working. Also if i write Files_in_queue i get the same output. This is my output statement: 

$dataSet.Tables[0] |
  Select-Object * -ExcludeProperty RowError, RowState, ItemArray, HasErrors, Table |
  ConvertTo-Html –Body $body |
  Out-File -Append $OutputFile

It's showing me the result of above mentioned SQL statement but with a * in the table header, not with the AS-text.

This is how I get the SQL result:

$connectionDetails = "Provider=sqloledb; " +
                     "Data Source=$dataSourceActual; " +
                     "Initial Catalog=$databaseActual; " +
                     "Integrated Security=SSPI;"

$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommandActual,$connection
$connection.Open()

$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet)
$connection.Close()

$dataSet.Tables[0] |
  Select-Object * -ExcludeProperty RowError, RowState, ItemArray, HasErrors, Table |
  ConvertTo-Html –Body $body |
  Out-File -Append $OutputFile

Edit 2:

$props = $dataSet.Tables[0] |
         Select-Object * -ExcludeProperty RowError, RowState, ItemArray, HasErrors, Table -First 1 |
         ForEach-Object { $_.PSObject.Properties | Select-Object -Expand Name }

$dataSet.Tables[0] | ConvertTo-Html –Body $body -Property $props |
  Out-File -Append $OutputFile

Error:

ConvertTo-Html : System.Management.Automation.PSObject can not be converted in
the following type: {System.String, System.Management.Automation.ScriptBlock}.
Bei C:\temp\html_test.ps1:138 Zeichen:15
+ ConvertTo-Html <<<<  –Body $body -Property $props |
    + CategoryInfo          : InvalidArgument: (:) [ConvertTo-Html], NotSupportedException
    + FullyQualifiedErrorId : DictionaryKeyUnknownType,Microsoft.PowerShell.Commands.ConvertToHtmlCommand

Edit 3:

This is the convertion to string of $props:

[string]$props = $dataSet.Tables[0] |
    Select-Object * -ExcludeProperty RowError, RowState, ItemArray, HasErrors, Table -First 1 |
    ForEach-Object { $_.PSObject.Properties | Select-Object * -Expand Name }

and the output statement:

$dataSet.Tables[0] | ConvertTo-Html –Body $body -Property $props |
  Out-File -Append $OutputFile

How can I build a loop, that the above mentioned code is only applied for one-column tables? I tried this:

if ($dataSet.Length = 1) {
    [string]$props = $dataSet.Tables[0] |
        Select-Object * -ExcludePropertyRowError, RowState, ItemArray, HasErrors, Table -First 1 |
        ForEach-Object { $_.PSObject.Properties | Select-Object * -Expand Name }
} else {
    [string]$props = $dataSet.Tables[0] |
        Select-Object * -ExcludeProperty RowError, RowState, ItemArray, HasErrors, Table |
        ForEach-Object { $_.PSObject.Properties | Select-Object * -Expand Name }
}

I tried also $dataSet.Tables[0].Length in the if clause and -eq 1 as condition.

Upvotes: 0

Views: 760

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200453

The behavior you observe seems to happen when the input for ConvertTo-Html has just one property. You'd get the same column header if for instance you selected only the Table property:

... | Select-Object Table | ConvertTo-Html -Body $body

Output:

...
<table>
<colgroup><col/></colgroup>
<tr><th>*</th></tr>
<tr><td>Table</td></tr>
<tr><td>Table</td></tr>
...
</table>
...

The reason for this behavior isn't clear to me, but you can mitigate it by specifying the desired column title via the -Property parameter of the ConvertTo-Html cmdlet:

... | Select-Object Table | ConvertTo-Html -Body $body -Property 'Files in queue'

Output:

...
<table>
<colgroup><col/></colgroup>
<tr><th>Files in queue</th></tr>
<tr><td>Table</td></tr>
<tr><td>Table</td></tr>
...
</table>
...

You don't even need the Select-Object step here, since the -Property parameter of ConvertTo-Html already selects the specified property.

If you know that there'll be only one column and know the value you're going to use as the column title, you could define your query string like this:

$coltitle = 'Files in queue'
$sqlCommandActual = "SELECT count(*) AS `"$coltitle`" FROM ..."

and process the table like this:

$dataSet.Tables[0] |
  ConvertTo-Html –Body $body -Property $coltitle |
  Out-File -Append $OutputFile

If you don't know the column title (because you're calling a stored procedure or whatever) you can determine it on the fly like this:

$props = $dataSet.Tables[0] |
         Select-Object * -ExcludeProperty RowError, RowState, ItemArray,
           HasErrors, Table -First 1 |
         ForEach-Object { $_.PSObject.Properties | Select-Object -Expand Name }

$dataSet.Tables[0] |
  ConvertTo-Html –Body $body -Property $props |
  Out-File -Append $OutputFile

This works for a single or multiple properties alike.

Upvotes: 0

Related Questions