Powershell script to send SQL Server query results via e-mail.

03/03/2011 at 13:54 · Filed under PowerShell ·Tagged Powershell

I schedule this script daily to notify a customer of yesterday’s database updates.
col1 is just text
col2 is a smalldate timestamp
I use GETDATE()-1 for yesterday

###

$SqlServer = “mydbserver”
$SqlCatalog = “mydb”
$SqlQuery = “select col1, col2 from mytable where col2 >= GETDATE()-1 order by col2”
$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”
$body2 = @”
here are your query results via e-mail …
$body1
thanks…
“@
$emailFrom = “me@mydomain.com”
$emailTo = “you@yourdomain.com, him@hisdomain.com”
$subject = “subject text goes here”
$emailbody = $body2
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$smtpServer = “mail.mydomain.com”
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
###

References:
http://www.simonhartcher.com/?p=146
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/603af83a-8db5-48ce-b19c-74a823ebbece/#5f634de4-71ce-4954-ba06-5bc6a55df218

Permalink