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
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”) = 2
‘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
