No email client for alerts! Then apply here.

There is an old philosophical controversy "If a tree falls in the forest, and no one is around, does it still make a sound." If the tree fell on my car, then there would definitely be a sound - my scream. If the SQL server errors, nobody reads the logs, it does not crash and nothing is on fire should we care? I think so. But how do you remember to read the logs, handle all the day’s challenges, and get from server to server scattered throughout the forests and sites? Even if we had the time, event logs are located on the best seller reading list somewhere near the phone book. We are not implying the content is not important, but like the phonebook 99.9% of the content is not important to the reader and there is a lot of content.

Let me relate to you the tragic tale of the "Great Write Protect Incident at the Not Okay Corral." One dark and stormy night, the SQL tape backup failed. SQL server dutifully entered the event into the Event Logs. This process repeated every night. When the tape was changed and checked there was no data on the tape. The full and incremental backups for that week didn’t exist. Someone had enabled the write protect. We checked with everyone they all swore that it was "not me." An immediate search for Mr. NotMe was undertaken, but he has managed to remain undetected, undiscovered, unnoticed, unobserved, unobtrusive, and unperceived.

We knew that if we had Exchange or Outlook we could be notified by e-mail if a SQL job failed. We had neither. We decided that we needed a means to read the event logs and the ability to notify key personnel of certain errors and warnings. The following script reads the application log looking for any error. We set up ours to e-mail our tape monkey when a category of 6, EventCode of 17055 error happens (for the Not Okay Corral this is a SQL server 3041 backup job failure). It also uses a CDO method for the e-mail function.

You are not limited to a single event error. You can scan the system logs and advise the LAN Team of events of interest. It is also possible to scan the Security logs, but this is a trickier subject reserved for later discussion. The neat thing about this script is that you can send an alert relating to errors, like a transaction log fill in the msdb, that do not stop things but can affect performance or are precursors to gloom and doom. You can even select warnings of a specific type to alert you of less-serious but important issues.

The Changes:

The script is relative simple to use but you will need to change the following:

• strComputer = "yourSQLserver" add your server name or if you run it on the server just add a period. strComputer = "." You can also run it on your workstation using the strComputer = "." designation.
• The e-mail stuff, From user, Recipient(s), The subject, Text body, and Mailserver.

objEmail.From = "SQLLords@company.com"
objEmail.To = "tapemonkey@company.com,boss@company.com"
objEmail.Subject = "SQL ERROR"
objEmail.Textbody = "The sky is falling!  The Sky is falling!"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'name of notesserverhost ?
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "mailserver"
Note: quotations marks are required.
The Code (alert.vbs):
‘- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
‘ remove the comment marker (‘) from On Error Resume Next
‘ when code runs without error
‘ On Error Resume Next
‘ declaring variables
DIM strComputer,dtmDate,dtmDay,dtmMonth,dtmYear,strDate
DIM intCounter,intCount,dtmWritDate,dtmWritDateY
DIM dtmWritDateM,dtmWritDateD,strWritDate
‘ initializing some variables
intCounter = 0
strComputer = "yourSQLserver"
dtmDate = Now-1
dtmDay = Right(("0" & Day(dtmDate)),2)
dtmMonth = Right(("0" & Month(dtmDate)),2)
dtmYear = Year(dtmDate)
strDate = dtmYear & dtmMonth & dtmDay
‘ attaching to WMI provider on server
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
‘ the query
Set colLoggedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_NTLogEvent Where Logfile = 'Application' " & _
    "AND Type = 'Error' AND Category = 6 AND EventCode = 17055")
For Each objEvent in colLoggedEvents
dtmWritDate = objEvent.TimeWritten
dtmWritDateY = Left(dtmWritDate, 4)
dtmWritDateM = Mid(dtmWritDate,5,2)
dtmWritDateD = Mid(dtmWritDate,7,2)
strWritDate = dtmWritDateY & dtmWritDateM & dtmWritDateD
intCounter = intCounter + 1
If strWritDate >= strDate Then
intCount = intCounter
End If
Next
If intCount > 1 Then
‘ e-mail section
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "SQLLords@company.com"
objEmail.To = "tapemonkey@company.com,boss@company.com "
objEmail.Subject = "SQL ERROR"
objEmail.Textbody = "The sky is falling!  The Sky is falling!"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'name of notesserverhost ?
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "mailserver"
 objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
‘ end of e-mail section
End If
‘ - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The Query: The SQL query can be changed to meet your needs:

    ("Select * from Win32_NTLogEvent Where Logfile = 'Application' " & _
    "AND Type = 'Error'")

Which will send an e-mail when any error is written to the application log. This can be changed to:

   ("Select * from Win32_NTLogEvent Where Logfile = 'Application' " & _
    "AND Type = 'Error' AND Category = 6 AND EventCode = 17055")

Which only returns an e-mail when this very specific error occurs. We recommend that you start with the simpler version. As your needs become more focused, the responsibilities more defined, that is when you make the query more specific. Some errors might be automatically handled by your maintenance plan and you can set the query to ignore them.

Queries in VB scripting are written in WQL, similar to SQL, but there are differences. The & _ is a way to break up and concatenate a script to make it more readable.

Date handling:

"Now-1" gets yesterday’s date. It is broken up into component parts and reassembled as YYYYMMDD. The Right(("0" & Month(dtmDate)),2) is a trick to assure single month and days are preceded by a "0" and that the reassembled date will always have eight characters.

dtmDate = Now-1
dtmDay = Right(("0" & Day(dtmDate)),2)
dtmMonth = Right(("0" & Month(dtmDate)),2)
dtmYear = Year(dtmDate)
strDate = dtmYear & dtmMonth & dtmDay

The dtmWritdate object returned is either in Cuniform or Mesopotanian code, I am not sure which. We convert it to its component parts and reassemble to the YYYYMMDD to compare it to yesterdays result. This could be simplified, but we use the Month and Day components in another part of the actual script we use which is a little more complex.

   dtmWritDate = objEvent.TimeWritten
dtmWritDateY = Left(dtmWritDate, 4)
dtmWritDateM = Mid(dtmWritDate,5,2)
dtmWritDateD = Mid(dtmWritDate,7,2)
strWritDate = dtmWritDateY & dtmWritDateM & dtmWritDateD

CDO e-mail

If there is more than one error in the last two days the e-mail is sent. We chose to use a two day window in case the e-mail server is down for a day. It’s a comfort thing. It is wise to have some date determinant in the script otherwise you will get all errors based on your query for the entire length of the log.

More on e-mailing scripting with Exchange or CDO is found here: http://www.microsoft.com/technet/scriptcenter/scripts/message/default.mspx

Depending on your security set-up, this e-mail process may not be allowed. You option here is to replace the e-mail section with a line like: Wscript.Echo "THERE IS AN ERROR IN THE APPLICATION LOG." You would run the script from the command line as cscript alert.vbs and the message will appear. You can then run the bonus script - "cscript event.vbs > eventError.txt." Note: Change the server array to the specific server with the error and change the query to report only error ids, dates and messages. Either delete the unwanted lines or use a single quote to comment them out.

Rest of Script:

We connect to the provider and query Win32_NTLogEvent Logfile =application , Type = error. If one or more errors occurred within the last two days we index the counter variable intCount. If the intCount is greater than or equal to one then an e-mail will be sent using the CDO method. Telling you to go an read the application event log for errors within the last two days.

The batch file: alert.bat

Note that the batch file is run using cscript and not wscript. Running any of these scripts from wscript could have you closing message boxes for the next century. You can use the Windows Scheduler or a third party scheduler to run the batch file at whatever interval you want.

Contain your excitement but we are adding a Bonus script. The following "event.vbs" will extract the events in your application and system event logs. Copy and paste it. Call it event.vbs and run it from a command line c:> cscript event.vbs > c:\event.txt . Note: do not run it under wscript or event.vbs > c:\event.txt unless you have changed the default to cscript. Also the command Ctrl-C stop the script.

Select a comfy chair and have a long, long, long evenings read . . . yawn! The value of this is to help you determine what objects to add to your query to make it more specific to your needs making the script actually useful

Notice also that the date codes are thoughtfully converted from Mesopotanian into English for you at no additional charge.

The bonus script: Event.vbs: (This script was generated using the ScriptoMatic 2 - available from the Microsoft Web Site - http://www.microsoft.com/technet/scriptcenter/tools/scripto2.mspx)

‘ - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
cscript Alert.vbs
‘ - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
‘ - - - - - - - - - - - - - - - - - - - - - - - - - - -
On Error Resume Next
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
‘  The following can be an array ("Server1","Server2",.,.,.,"ServerN")
‘  But be careful this can return a lot of data, the (".") returns info from your computer.
arrComputers = Array(".")
For Each strComputer In arrComputers
   WScript.Echo
   WScript.Echo "=========================================="
   WScript.Echo "Computer: " & strComputer
   WScript.Echo "=========================================="
   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NTLogEvent", "WQL", _
                                          wbemFlagReturnImmediately + wbemFlagForwardOnly)
   For Each objItem In colItems
      WScript.Echo "Category: " & objItem.Category
      WScript.Echo "CategoryString: " & objItem.CategoryString
      WScript.Echo "ComputerName: " & objItem.ComputerName
      strData = Join(objItem.Data, ",")
         WScript.Echo "Data: " & strData
      WScript.Echo "EventCode: " & objItem.EventCode
      WScript.Echo "EventIdentifier: " & objItem.EventIdentifier
      WScript.Echo "EventType: " & objItem.EventType
      strInsertionStrings = Join(objItem.InsertionStrings, ",")
         WScript.Echo "InsertionStrings: " & strInsertionStrings
      WScript.Echo "Logfile: " & objItem.Logfile
      WScript.Echo "Message: " & objItem.Message
      WScript.Echo "RecordNumber: " & objItem.RecordNumber
      WScript.Echo "SourceName: " & objItem.SourceName
      WScript.Echo "TimeGenerated: " & WMIDateStringToDate(objItem.TimeGenerated)
      WScript.Echo "TimeWritten: " & WMIDateStringToDate(objItem.TimeWritten)
      WScript.Echo "Type: " & objItem.Type
      WScript.Echo "User: " & objItem.User
      WScript.Echo
   Next
Next
Function WMIDateStringToDate(dtmDate)
WScript.Echo dtm:
WMIDateStringToDate = CDate(Mid(dtmDate, 5, 2) & "/" & _
Mid(dtmDate, 7, 2) & "/" & Left(dtmDate, 4) _
& " " & Mid (dtmDate, 9, 2) & ":" & Mid(dtmDate, 11, 2) & ":" & Mid(dtmDate,13, 2))
End Function
‘ - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The use of vbs to get SQL server information is a valuable tool to allow hands free administration, filtering vast amounts of information and making sure the server is healthy. This is just another tool in your arsenal that you can make very general or super specific as your needs dictate. We also use this script to monitor other non-Microsoft OEM database application functions proactively preventing troubles. Since we now monitor the tape function, Mr. NotMe seems to have left the building. Hope you find it a helpful tool.

Caveat: Like any script you download or discover in a magazine or book, try this script in a development environment, not on a production server. You do not want to slow up a production server if you have issues. You can even run this on your work station to examine your logs before taking it to the SQL world.