Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

An alternative to SQL Server email alerts


By:   |   Read Comments (1)   |   Related Tips: More > Database Mail

Problem

In a tip on How to setup SQL Server alerts and email operator notifications, you have seen how you can configure email alert notifications in your SQL Server instance. There are cases where we cannot use SQL Server alerts like in a SQL Server 2005 Express instance or a clustered SQL Server 2000 instance as the MAPI interface used by SQL Mail is not cluster-aware. What other alternatives do we have for sending email alert notifications?

Solution

Sending email alert notifications is one way of letting Database Administrators (DBAs) know of the status of an automated job. We use it to let us know if a database backup failed, an ETL job did not complete in time and similar other scenarios. SQL Server uses SQL Server Agent to do this task together with SQL Mail in SQL Server 2000 and Database Mail in SQL Server 2005. But without SQL Server Agent, like in SQL Server 2005 Express, we will not be able to use Database Mail. What we can do is use VBScripts or PowerShell scripts that can send emails using SMTP. We can then call these scripts from inside our stored procedures or from automated jobs using Windows Task Scheduler to enable us to send email alert notifications. Below is an example of a VBScript that can be used to send out emails. Save the file as sendEmailSMTP.vbs.

VBScript file that sends out email using SMTP

'Accept input parameters
Dim jobType

'first  parameter
jobType= Wscript.Arguments.Item(0)

Set objMessage = CreateObject("CDO.Message"
objMessage.Subject = "Message Alert from SQL Server: "  & jobType & "  job failed"
objMessage.From = "admin@domain.local" 
objMessage.To = "sqladmin@domain.local" 
objMessage.TextBody = "The " & databaseName & " " & jobType & " job running in the SQL Server instance failed on " & Now() & vbCrLF & vbCrLf & "Please look at this problem ASAP " & vbCrlf & vbCrlf & vbCrlf & vbCrlf  & "- The SQL Server Administrator/DBA -"

'This section provides the configuration information for the remote SMTP 
'server.Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.domain.local"

'Server port number(typically 25)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 

objMessage.Configuration.Fields.Update

objMessage.Send
Set objMessage = Nothing

The script accepts a parameter which you can customize which is the job type. You can customize the script with your own message, subject, sender and recipient addresses as well as the SMTP server address. An example of using this script would be to send email alerts in case a daily backup job failed as highlighted in the Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files tip. You can generate an output file with the sqlcmd.exe utility and read the results of the output file. The call to the sqlcmd.exe utility would look something like this:

sqlcmd -S<INSTANCENAME>-E -i"E:\SQL_Backup\scripts\backupDB.sql" -o"E:\SQL_Backup\scripts\output.txt"

Below is the VBScript code to read the output file and sends an email alert should the output file contain a specific error message pertaining to backup database command failing. It looks for the existence of the phrase "BACKUP DATABASE successfully processed" in the output file and, if not found, calls the VBScript file to send the email alert notification. Save the file as checkErrorLog.vbs.

VBScript file to read the output file and send email notifications

strFileNameResults="E:\SQL_Backup\scripts\output.txt"
strParentFolder
="E:\SQL_Backup\scripts\"

Const ForReading = 1

Set objShell = CreateObject("Wscript.Shell"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileNameResults, ForReading)
strContents = objFile.ReadAll

'The value=0 means that it cannot find the phrase RESTORE LOG successfully 
'processed and thus means an error
If Instr(strContents,"BACKUP DATABASE successfully processed")=0 Then
  
objShell.Run(strParentFolder & "scripts\sendEmailSMTP.vbs BACKUP ")
End If

objFile.Close

Set
objFSO=Nothing
Set
objFile=Nothing
Set
objShell=Nothing

You can then add a call to this VBScript file in your automated SQL Server job - be it a Scheduled Task in Windows or a call using xp_cmdshell.

Next Steps
  • Do not let a missing SQL Server Agent prevent you from sending email alert notifications on your automated SQL Server jobs. As a DBA, you need to be informed of whether your automated jobs complete successfully or not, even if it is running a SQL Server 2005 Express instance
  • Implement this script together with the scripts provided in the Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files tip
  • Customize this email alert notification script to be flexible enough to be called by any automated SQL Server job


Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, March 03, 2010 - 2:22:41 PM - ecpcwallace Back To Top

I'm trying to get this script to work and I'm having problems with this section:

'This section provides the configuration information for the remote SMTP 
'server.Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing"

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver""smtp.domain.local"

'Server port number(typically 25)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport"25 

objMessage.Configuration.Fields.Update
 

My SMTP server is an exchange server and the SMTP port is 192.168.1.11 and port number is 25.

 i did change teh "smtp.domain.com to 192.168.1.11 but I'm getting an error.

What needs to be edited on the script to make it work? Do I have to change or remove: (http://schemas.microsoft.com/cdo/configuration/smtpserver

Thanks

Chuck

 


Learn more about SQL Server tools