Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    21

    Unanswered: 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.



    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When the Agent service fails, or just when a job fails?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2006
    Posts
    21
    When the Agent Service Fails.

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    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).

    Code:
    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
    Have you hugged your backup today?

  6. #6
    Join Date
    Sep 2006
    Posts
    21
    Hello

    Thanks for your script.

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

    Thanks

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by dvaddi
    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
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •