How to setup an e-mail alert system for ApexSQL tools

Applies to
All ApexSQL console applications with Command line interfaces (CLI)

Summary
This article describes how to create e-mail alert system for ApexSQL tools using PowerShell in order to send e-mails upon executed ApexSQL tool providing the necessary information.

Description

In the following example, we’ll setup e-mail alert system, using a PowerShell script, that will send e-mails upon each ApexSQL Diff (an ApexSQL tool that will be used) execution, along with the appropriate message and attachment.

Also, once the PowerShell script is created, it will be scheduled to compare and synchronize two databases, generate a date stamped HTML report and a log file.

Creating e-mail function

The first thing in this setup, let’s create a function in the PowerShell script for sending e-mails. In this example gmail is used as e-mail provider. First, let’s define function, e-mail account credentials, sender, and recipient:

function SendMail($subject, $text, $attachment)
{

    $SecurePassword = "PaSSw0rd" | ConvertTo-SecureString -AsPlainText -Force
    $from = "fromnoname@gmail.com"
    $to = "tononame@gmail.com"
    $Credentials = New-Object System.Management.Automation.PSCredential ("fromnoname", $SecurePassword)
    $smtpServer = 'smtp.gmail.com'

Now, we’ll define variables for e-mail properties and options when sending attachments:

$mailprops=@{
        Subject = $subject
        Body = $text
        To = $to
        From = $from
        SmtpServer = $smtpServer
        UseSsl = $true
        Port = 587
        Credential = $Credentials
    }

        try
        {
            if($attachment -ne $null)
            {
                Send-MailMessage @mailprops -ErrorAction:Stop -Attachments:$attachment
            }
            else
            {
                Send-MailMessage @mailprops -ErrorAction:Stop
            }
            return "Mail succesfully sent`r`n" 
            #or return $true
        }
        catch
        {
            return ("Sending e-mail failed: " + $_.Exception + "`r`n")
            #or return $false
        }
}

If the e-mail function doesn’t work, i.e. it provides the below error message:

And you get the following e-mail:

Open your gmail account, by clicking on the account picture in the top-right corner and click the My Account button:

Under the Sign-in & security tab, click the Connected apps & sites, and turn ON the Allow less secure apps option:

If everything else is set up correctly, you’ll get the “Mail successfully sent” sent message.

Defining locations

In the following part, we’ll work on defining needed variables for different location. First, let’s create a function that will check for existence of the Report and Log folders and that will create it if they don’t exist:

#checks for existence of Reports or Logs folders, and creates it if it is not created
function CheckAndCreateFolder
{
    param 
    (
        [string] $rootFolder, 
        [switch] $reports, 
        [switch] $logs
    )

    $location = $rootFolder

    #set the location based on the used switch
    if ($reports -eq $true)
    {
        $location += "\Reports"
    }
    if ($logs -eq $true)
    {
        $location += "\Logs"
    }
    #create the folder if it doesn't exist and return its path
    if (-not (Test-Path $location))
    { 
        mkdir $location -Force:$true -Confirm:$false | Out-Null 
    }
    return $location
}

Next, let’s define variables for the root folder, where all folders and files will be created:

#defining variable for the recognizing the current path 
$currentPath = (Split-Path $SCRIPT:MyInvocation.MyCommand.Path)

#root folder of the system
$rootFolder = "$currentPath"

#output files location 
$reportsLocation = CheckAndCreateFolder $rootFolder -Reports
$logsLocation    = CheckAndCreateFolder $rootFolder -Logs

Setting application variables

The next part in the PowerShell script will define variables for ApexSQL Diff location, date stamp, and switches that will be used:

#ApexSQL Diff location, date stamp variable is defined, along with tool’s parameters 
$diffLocation  = "ApexSQLDiff"
$dateStamp = (Get-Date -Format "MMddyyyy_HHMMss") 
$diffSwitches = "/pr:""$rootFolder\SchemaSync.axds"" /ot:html /hro:d s t is /on:""$reportsLocation\SchemaReport_$dateStamp.html"" /out:""$logsLocation\SchemaLog_$dateStamp.txt"" /sync /v /f /rece"

Learn more about How to use a project file in the CLI.

ApexSQL Diff will be called with the following expression, along with the specified switches, initiate the comparison and synchronization process, and create date stamped HTML report and a log file, and determine the return code:

#initiate the comparison and synchronization process
(Invoke-Expression ("& `"" + $diffLocation +"`" " +$diffSwitches))
$returnCode = $lastExitCode

In this example, Windows authentication was used, but SQL Server authentication can be used as well and login passwords will be encrypted in the saved project file. Find out more about other ways handling database/login credentials during automated execution via the CLI.

Utilizing e-mail function

The last part of the PowerShell script is to utilize the created e-mail function (SendMail) and define when it will be used and what will be sent.

There are three potential outcomes in ApexSQL Diff’s case:

1. Differences are detected – synchronization is completed, a date stamped HTML report is generated and e-mail is sent with attached HTML report
2. No differences are detected – there is nothing to synchronize, the generated HTML report will be removed and an e-mail won’t be sent
3. An error occurred – an issue occurred during the application execution, date stamped log file is generated and e-mail is sent with attached log file

 #differences detected
if($returnCode -eq 0)
{
    $text = "Differences are detected.`r`nPlease check attached report file"
    $attach = "$reportsLocation\SchemaReport_$dateStamp.html"

    SendMail -subject "ApexSQL Diff synchronization results" -text $text -attachment "$attach"
    "`r`nThere are differences and HTML report is created. Return code: $returnCode" >> "$logsLocation\SchemaLog_$dateStamp.txt"
    exit
}

#there are no differences or an error occurred
else
{
    #remove the newly created report, since no differences are detected
    if(Test-Path "$reportsLocation\SchemaReport_$dateStamp.html")
    { 
        Remove-Item -Path "$reportsLocation\SchemaReport_$dateStamp.html" -Force:$true -Confirm:$false 
    }
    "`r`nThere are no differences and latest report is deleted. Return code: $returnCode" >> "$logsLocation\SchemaLog_$dateStamp.txt"

    #an error occurred
    if($returnCode -ne 102)
    {
        "`r`nAn issue occurred during the application execution at $dateStamp.`r`nReturn code: $returnCode`r`n" >> "$logsLocation\SchemaLog_$dateStamp.txt"
        $text = "An issue occurred during the application execution at $dateStamp.`r`nPlease see the attached log file for details"
        $attach = "$logsLocation\SchemaLog_$dateStamp.txt"
        
        SendMail -subject "ApexSQL Diff synchronization error" -text $text -attachment "$attach"  
    }    
}

Reviewing e-mails

Now that everything is set up, let’s try to run the PowerShell script attended. If everything is ok, we’ll get e-mail with the “ApexSQL Diff synchronization results” subject with an attached HTML report.

However, if there is some issue, an e-mail with the “ApexSQL Diff synchronization error” subject will be received with attached log file:

If we open the attached log file, we’ll see the following message:

An issue occurred during application execution at 03012017_140333.
Return code: 2
Error description: Switch ‘tn’ is not recognized

If we check the article about General usage and the common Command Line Interface (CLI) switches for ApexSQL tools, under the Common return error codes section, we’ll see that /tn switch is an invalid one and it doesn’t exist.

In order to fix this issue, we’ll locate the /tn switch in the PowerShell script and replace it with the /on switch, the correct one for defining path and name of the output file (HTML report in this case) and now everything will run smoothly.

Scheduling the process

Since everything is up and running and everything is automated in the desired way, this process can be scheduled in different ways. Learn more about the ways of scheduling ApexSQL tools.

Downloads

Please download the script(s) associated with this article on our GitHub repository

Please contact us for any problems or questions with the scripts.