Mailing DBA when sql server Agent Fails

Hello

I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.

Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.



ThanksWhen the Agent service fails, or just when a job fails?|||When the Agent Service Fails.

Thanks|||Since the agent executes the scripts, how do you expect it to execute a script to notify you that it is no longer executing scripts?

Two alternatives:
1) Set up an agent on another server to check the functioning of the first one.
2) Set a script to run when the service is restarted (note that this will NOT notify if the server crashes and does not automatically restart).|||I use a configurable WMI script to check the status of selected services on selected servers. I check for any that are set to auto start and are not in a running state (or status, can't remember which).

Function CheckServices ( sComputer, sFQDN )

Dim oConnMail
Dim oCommMail

Dim oWMIService
Dim oItems
Dim oItem

Dim sDisplayName
Dim sSubject
Dim sMessage

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

' Define objects for mail message
Set oConnMail = CreateObject("ADODB.Connection")
Set oCommMail = CreateObject("ADODB.Command")

' Open connection to mail database
oConnMail.ConnectionString = Replace(sConnectionString, sDatabase, "master")
oConnMail.Open

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' This part of the routine verifies that
' services that are set to Auto start are still running;
' if not running, then an alert is e-mailed
If oItem.StartMode = "Auto" and oItem.State <> "Running" Then

oCommMail.CommandText = "spSendMail"
oCommMail.CommandType = 4
oCommMail.ActiveConnection = oConnMail

sSubject = "Service " & oItem.Name & " stopped running on " & oItem.SystemName
sMessage = "Service " & sDisplayName & " stopped running on " & oItem.SystemName & VbCrLf & _
"Service Specific Exit Code: " & oItem.ServiceSpecificExitCode & vbCrLf & _
"Exit Code: " & oItem.ExitCode & vbCrLf & _
"Status: " & oItem.Status & vbCrLf & _
"State: " & oItem.State
' sMessage = "Test"
oCommMail.Parameters.Refresh
oCommMail.Parameters("@.Subject") = sSubject
oCommMail.Parameters("@.Message") = sMessage
oCommMail.Parameters("@.Recipient") = sRecipient

oCommMail.Execute

End If
Next

' Close mail database objects
Set oCommMail = Nothing
oConnMail.Close
Set oConnMail = Nothing
Else
Call AddLogEntry(sFQDN, "Error", "ServicesCheck", Err.number & " - " & Err.Source & " - " & Err.Description)
Err.Clear
End If

oConn.Close

End Function|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks

I have a management server which is a dumping ground for a lot of different monitoring tools (some COTS, some custom). This script is scheduled from this server using Windows Scheduled Tasks. I pass in selected parameters to the script from the command line.

WMI is native to Windows 2000/Server 2003; you shouldn't need to load anything special there. You may need to adjust your permissions on the target server to be able to read from the WMI repository (I think you need to be at least a Power User, but I could be wrong.

Note that the script provided is a function. It would run inside of a main script that would:

1. Pull in a list of servers to poll
2. Pull in any system parameters (such as the e-mail address(es) you want the alert to go to
3. Call the function (passing in the name of the computer)

Regards,

hmscott