VBScript: Forward SQL Server Alerts/Windows Events to (Cingular) SMS

Someone recently asked why I don't use SQL Mail on my SQL Servers (2000 and below). The answer is: I don't want to install Outlook on my SQL Servers. Sure, there are ways around it but I prefer just using "net send" to alert the Operator (moi) then forwarding those messages on to my inbox and phone.

Although my employer offered me a Blackberry, I never really cared for them. The battery life is just too short and I don't like talking into something the size of my hand. So.. I use a regular phone that doesn't receive e-mails easily. It does, however, love to receive texts. Cingular even gave me an email address to go with my phone and this is what I use to Alert me if any of my jobs (Backups, Optimizations, etc) fail.

First, I created a DNS CNAME record to my workstation called DBAWorkstation. Then I created an operator named DBA Workstation. I like to keep things generic in the event that I leave or my workstation name changes. In the event that happens, there's just one change at the DNS level. So next, I set the jobs to alert this operator via net send if any jobs fail. Finally, I set up a script that runs all the time on my workstation which has IIS's SMTP installed locally: '**************************************************************************** ' This script created by Chrissy LeMaire ([email protected]) ' Website: https://netnerds.net/ ' ' NO WARRANTIES, etc. ' ' This script monitors Windows Events and forwards any Application Popups ' to an email address '*****************************************************************************

' Push Event Viewer Alert Set objWMIService = GetObject("winmgmts:{(security)}!root/cimv2") Set eventSink = wscript.CreateObject("WbemScripting.SWbemSink", "EVSINK_") strWQL = "Select * from __InstanceCreationEvent where TargetInstance isa 'Win32_NTLogEvent' and TargetInstance.SourceName = 'Application Popup' and TargetInstance.EventCode = 26" objWMIService.ExecNotificationQueryAsync eventSink,strWQL

' Keep it going forever While (True) Wscript.Sleep(1000) Wend

Sub EVSINK_OnObjectReady(objObject, objAsyncContext) 'Here's where I do a whole lot of cleaning. Edit to your liking. strCleanedMsg = replace(objObject.TargetInstance.Message,"Application popup: Messenger Service : ","") strCleanedMsg = replace(strCleanedMsg,"Message from ","") strCleanedMsg = replace(strCleanedMsg," to " & objObject.TargetInstance.ComputerName,"") strCleanedMsg = replace(strCleanedMsg," for DB Maintenance Plan 'DB Maintenance for All DBs'","") strCleanedMsg = replace(strCleanedMsg,"JOB RUN:","") strCleanedMsg = replace(strCleanedMsg," was run on "," failed around ") strCleanedMsg = Left(strCleanedMsg,InStr(strCleanedMsg,"DURATI")-2) If Len(strCleanedMsg) > 159 Then strCleanedMsg = Left(strCleanedMsg,159) ' SMS allows max 160 characters. I picked 159 just in case.

Set objCDO = CreateObject("CDO.Message") objCDO.From = "SQL Alert " objCDO.To = "[email protected]" ' Phone number goes there. Cingular does the SMS magic objCDO.CC = "[email protected]" objCDO.TextBody = strCleanedMsg objCDO.Configuration.Fields.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 1 'cdoSendUsingPort (1 = local, 2= remote, 3 = Exchange) objCDO.Configuration.Fields.Update objCDO.Send set objCDO = Nothing End Sub Run that script as a service and you're set with SMS/E-mail notifications in SQL Server 2000 and below. SQL Server 2005 can do the same thing..but e-mail is much more accessible within the service itself.