Convert query output into email

Question

Post reply

Convert query output into email

Bruin

SSCrazy

Points: 2058

February 7, 2023 at 12:16 pm

#4146485

How can I take the output from this query and turn that into an email?

Thanks.

$queryData = "SELECT '$Capuretbl',Max(DateStored),getdate() FROM $($Row[0])"


  
$command.CommandText = $queryData
$command.Connection = $connection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16161 More actions · Answer 1

and what have you tried so far? there are too many examples of sending email from powershell - some full blown as you seem to wanna do.

we are not here to do your work neither research for you, so you do need to show us what you did - and just putting some lines without showing some effort is not going to do it either.

Bruin SSCrazy Points: 2058 More actions · Answer 2

Trying to goggle now to find eamples based upon what I have as Input above.

Bruin SSCrazy Points: 2058 More actions · Answer 3

This works but sends an email for every table listed in query .. It's the "For Each" ... How can I issue just 1 email and list all of the

results in that 1 email. I also had more fields in the output that the 2 derived from query.

Thanks.

$SqlAdapter.Fill($DataSet) | Out-Null


$table = $DataSet.tables


$html = "<table><tr><td>Table</td><td>Last Stored Date</td></tr>"
foreach ($row in $table.Rows)
{
    $html += "<tr><td>" + $row[0] + "</td><td>" + $row[1] + "</td></tr>"
}
$html = $DataSet.Tables[0] | ConvertTo-Html -Fragment

# Send the email
$smtpserver = "xxxx"
$from = "xxxxx"
$to = "xxxxxxxx"
$subject = "Doc Repository Status"
$body = "Hi there,<br />Here is a report listing all the Doc Repository Tables and Last Update Status:<br /><br />" + $html
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml



$connection.Close()

frederico_fonseca SSCoach Points: 16161 More actions · Answer 4

you are setting $html twice - to different things - so as mentioned before for other things YOU need to DEBUG your scripts to see at what point you got it wrong - and fix accordingly.

Bruin SSCrazy Points: 2058 More actions · Answer 5

Okay I have the format correct but still getting 2 emails ..

How do I roll them all up in 1 email ?

If I look at output from:

$table = $DataSet.tables

$table -- this has exactly what I want to send 27 rows of data.

# Create column headers of Table1
$HtmlTable1 = "<table border='1' align='Left' cellpadding='2' cellspacing='0' style='color:black;font-family:arial,helvetica,sans-serif;text-align:left;'>
<tr style ='font-size:13px;font-weight: normal;background: #FFFFFF'>
<th align=left><b>TableName</b></th>
<th align=left><b>Last Updated</b></th>
</tr>"



# Insert data into Table1
foreach ($row in $table.Rows)
{ 
    $HtmlTable1 += "<tr style='font-size:13px;background-color:#FFFFFF'>
    <td>" + $row.Column1 + "</td>
    <td>" + $row.Column2 + "</td>
    </tr>"
}
$HtmlTable1 += "</table>"

frederico_fonseca SSCoach Points: 16161 More actions · Answer 6

if you are getting 2 emails it means your code is executing the sendmail twice or you are sending to more than one account (including a distribution group) and getting it twice cause of that - again its up to you to see what in your code is causing that.

Bruin SSCrazy Points: 2058 More actions · Answer 7

If I display $HtmlTable1 I can see both pieces, I want to send out via email..... If I then close my Initial

for each after that

$connection.Close()

}

email stuff then after that I get one email with only the last xml information in $HtmlTable1

<><><>

<><><><

If I close my for each after the email send I get 2 emails..

Bruin SSCrazy Points: 2058 More actions · Answer 8

I saw an example that combined multiple sql queries, and yes this does produce the desired results but I have no idea how to get this into the HTML format I need and seems like there could be an easier approach. Each time it picks a table to report on in the loop it's adding to $querydata. This example I found looks like you have to do a readset for each table statement in $querydata.. When I did run this piece of code I did see output I was looking for.


$queryData = "SELECT '$Capuretbl',Max(DateStored) FROM $($Row[0])"


 
$readAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$readSet = New-Object System.Data.DataSet  
$command.CommandText = $queryData
$command.Connection = $connection
$readAdapter.SelectCommand = $Command
$readAdapter.Fill($readSet) |out-null
$Connection.Close()
Foreach ($row in $readSet.Tables[0].rows) {
   Write-Output "Do what you want with the first query results here"
   Write-Output "$($row.column1)  $($row.column2)"
}
Foreach ($row in $readSet.Tables[1].rows) {
   Write-Output "Do what you want with the second query results here"
    Write-Output "$($row.column1)  $($row.column2)"