• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 47
  • Last Modified:

Powershell htmlout not working. Get sql result in html form.

Dear experts, this code below is not working,
I think $htmlout = select-object count| convertto-html -head $a
is not really work as thought, I want to show the sqlQuery result in a html file and I use: "select-object count" Now Im not sure if i used it right. the result is blank. Nothing. Anyone can help me help me solve this?

Thanks

$SQLServer = "SQL01\Trans"
$SQLDBName = "Runtime"

$SqlQuery = "

SELECT (
SELECT count(*)
FROM [Prod].[Data].[IncomingUpdate_fd]
WHERE InsertTime >= DATEADD(day,-10, getdate())
)As Incoming Prod,
(
SELECT count(*)
FROM [Prod].[Data].[IncomingUpdate_fd]
WHERE InsertTime >= DATEADD(day,-10, getdate()) AND IsProcessed = 'P'
)As Incoming Processed,
(
SELECT count(*)
FROM [Prod].[Data].[IncomingUpdate_fd]
WHERE InsertTime >= DATEADD(day,-10, getdate()) AND IsProcessed is null
)As Not Processed

"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; user id = XXXX; password = XXXX"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet)
 
$SqlConnection.Close()

$a = "<style>"
$a = $a + "BODY{background-color:white;font-family:verdana;font-size:12px;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;padding:3px}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;padding:3px}"
$a = $a + "</style>"

$htmlout = select-object count| convertto-html -head $a
0
WeTi
Asked:
WeTi
1 Solution
 
Dustin SaundersDirector of OperationsCommented:
So, your issue is that you are using Select-Object on nothing, then converting that to html.

So what you would want is something like this to select the properties:

$htmlout = $DataSet | ConvertTo-Html -Head $a -Property 'Incoming Prod','Incoming Processed','Not Processed'

Open in new window

1
 
WeTiAuthor Commented:
working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now