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

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
$results = $DataSet.Tables | format-table -autosize | out-string
$body1 =”$results”
$body2 = @”
here are your query results via e-mail …
$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)




  1. satis said

    The in the query,I have some clarifications.In the above script it shows errors for the following lines
    Do i need to change anything for these lines??
    And $SQL Catalog is dataase name?

  2. lukieb said

    Yes $SQL Catalog is the database name.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: