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
$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

About these ads

2 Comments »

  1. satis said

    Hi
    The in the query,I have some clarifications.In the above script it shows errors for the following lines
    $SqlAdapter.Fill($DataSet)
    $DataSet.Tables[0]..
    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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: