I currently have a script set up that runs every hour and looks up the value of an order is over 100,000 in my SQL  database. if the query returns with a result it emails a certain user letting them know the details of the order. The only problem is that it sends a blank email if no results are found.  How can I change this to only send an email when there are rows in the query?

My current script looks like this;

$SqlServer = MySQLserver
$SqlCatalog = MyDB
$SqlQuery = select TotalCost from OrderTable
where TotalCost >= 100000
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = Server = $SqlServer; Database =
 $SqlCatalog; Integrated Security = True
 $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
 $SqlAdapter.Fill($DataSet)
 $SqlConnection.Close()
 $DataSet.Tables[0]
 $results = $DataSet.Tables | format-table -autosize | out-string
 $body1 =$results

 $emailFrom = SQLserver@mycompany.com
 $emailTo = TheMainMan@mycompany
$subject = Order Over 100,000
$emailbody = $body1
 $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
 $smtpServer = myMailserver
 $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
 $smtp.Send($message)
 ###

Like this

 if($emailbody -ne $null)
 {
 $smtp.Send($message)
 }

$emailFrom = SQLserver@mycompany.com
 $emailTo = TheMainMan@mycompany
$subject = Order Over 100,000
$emailbody = $body1
 $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
 $smtpServer = myMailserver
 $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
 if($emailbody -ne $null)
 {
 $smtp.Send($message)
 }

Thanks for getting back to me Chen.

However I am still receiving a blank email.

Please use a trigger on  table to notify you by email of order exceeding value.

Why is everyone creating new variables.

$results is what you want.  Why assign it to $body then to $emailbody.

Please take the time to learn coding basics and PowerShell basics. It will save you a lot of wasted time.

Wow! Thanks for the input.

First of all a trigger on my table is not what the question was about.

Also is that not the purpose of a forum where people who are trying to learn, can ask questions and see what they are doing wrong, correct their mistakes and get a non condescending answer ??? If not perhaps I should have posted this on Reddit. 

And even when using $results in the if statement I still receive a blank email.

Wow! Thanks for the input.

First of all a trigger on my table is not what the question was about.

Also is that not the purpose of a forum where people who are trying to learn, can ask questions and see what they are doing wrong, correct their mistakes and get a non condescending answer ??? If not perhaps I should have posted this on Reddit. 

And even when using $results in the if statement I still receive a blank email.

 

Stop being defensive.  You are getting feedback and no one has tried to erase you.

The remark was a dig to get everyone's attention and not aimed exclusively at you.  It is intended to make you think.  Remember "Wash-On, Wash-Off",  The master says do it my way until you are smart enough to understand.

First eliminate all unnecessary code then fix the real problems.

Look at you logic.  What happens if no records are returned from your query.

Heads up!

if($DataSet.Tables[0].Count){
    $body=$DataSet.Tables[0]| format-table -autosize | out-string
    Send-MailMessage -Body $body -From SQLserver@mycompany.com -To TheMainMan@mycompany -subject 'Order Over 100,000' -SMTPServer smtpServer 
}