SQL Server – sending email tsql + powershell


Today, I wanted to send an email when a long-running script completed successfully. The SQL server was already configured correctly with the Database Mail Configuration Wizard, so I just had to add similar code as code below to send the mail:

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'john.doe@someplace.com',
    @body = 'The custom script was successfully completed',
    @subject = 'Automated Success Message' ;

In addition, yesterday I had to send email from a powershell script to notify owner of Sharepoint sites. Here is my send_mail function used from powershell:

function send_email ([string]$emailTo, [string]$emailFrom, 
			[string]$emailSubject, [string]$emailBody)
{
    $emailSmtpServer = "smtp.company.com"
    $emailSmtpServerPort = "25"
    $emailSmtpUser = "COMPANY\johndoe"
    $emailSmtpPass = "johndoe"

    $emailMessage = New-Object System.Net.Mail.MailMessage
			( $emailFrom , $emailTo )
    $emailMessage.IsBodyHtml = $true
    $emailMessage.Subject = $emailSubject
    $emailMessage.Body = $emailBody 

    $SMTPClient = New-Object System.Net.Mail.SmtpClient
			( $emailSmtpServer , $emailSmtpServerPort )
    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential
			( $emailSmtpUser , $emailSmtpPass );

    $SMTPClient.Send( $emailMessage )
 }

Off course, I need to rework the security here and not hard code the password, but for testing purpose, this worked smoothly yesterday.

Happy emailing 🙂

Advertisements

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