Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

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

Mail Using SMTP Server

Have anyone been able to set up mail using a SMTP server successfully?
Is it necessary to have a mailbox for outgoing mail on the SMTP server?
Thanks
LystraTry this.

C:\Backup\Db Maint SMTP Alerter.htm|||Please explain yourself, is this suppose to be a link?

Lystra|||Sorry, I put the wrong address. Try this: http://sqldev.net/xp/xpsmtp.htm. On this website , you can found the XPsmtp configuration, I even used it on my server a couple months ago, they work well.

Mail Task in SSIS

Please help me i am new to ssis ....I am trying to send an email using mail task and I am sure that ISP works fine with smtp. I am entering all the correct information in the task but it takes a long time to run the task and then comes back with a failure error. can someone help me find an easy way to send an email to group of emails...I even tried to send it to 1 email and it is still not working.What error message do you get?|||

Thank you for the quick response....when I have the windows authontications on I don't get any email sent but when I uncheck that optiong I can only send emails withing exchange addresses and not any email address..is there a work around this...or do I have to use a script ...(if so can you please help me with the script)

thanks, Emad

|||Talk to your exchange server administrator - whether you need authentication, and why the server does not allow sending mails to outside people. SSIS is just a client that submits mails to server, we are testing that it can do it successfully, but what happens to the mail on the server is dependent on mail server configuration and completely outside of SSIS control.|||

use outlook client on the same machine (as SQL box) and see if you have the same problem, if the you have the same issue using outlook then this an exchange issue, if outlook can send it OK, then you have the investigate the problem on SQL.

you also can set up DatabaseMail in SQL 2005 and test the send mail process then use following T-SQL to send mail

sp_send_dbmail [ [ @.profile_name = ] 'profile_name' ]

[ , [ @.recipients = ] 'recipients [ ; ...n ]' ]

[ , [ @.copy_recipients = ] 'copy_recipient [ ; ...n ]' ]

[ , [ @.blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]

[ , [ @.subject = ] 'subject' ]

[ , [ @.body = ] 'body' ]

[ , [ @.body_format = ] 'body_format' ]

[ , [ @.importance = ] 'importance' ]

[ , [ @.sensitivity = ] 'sensitivity' ]

[ , [ @.file_attachments = ] 'attachment [ ; ...n ]' ]

[ , [ @.query = ] 'query' ]

[ , [ @.execute_query_database = ] 'execute_query_database' ]

[ , [ @.attach_query_result_as_file = ] attach_query_result_as_file ]

[ , [ @.query_attachment_filename = ] query_attachment_filename ]

[ , [ @.query_result_header = ] query_result_header ]

[ , [ @.query_result_width = ] query_result_width ]

[ , [ @.query_result_separator = ] 'query_result_separator' ]

[ , [ @.exclude_query_output = ] exclude_query_output ]

[ , [ @.append_query_error = ] append_query_error ]

[ , [ @.query_no_truncate = ] query_no_truncate ]

[ , [ @.mailitem_id = ] mailitem_id ] [ OUTPUT ]|||

use outlook client on the same machine (as SQL box) and see if you have the same problem, if the you have the same issue using outlook then this an exchange issue, if outlook can send it OK, then you have the investigate the problem on SQL.

you also can set up DatabaseMail in SQL 2005 and test the send mail process then use following T-SQL to send mail within SSIS

sp_send_dbmail [ [ @.profile_name = ] 'profile_name' ]

[ , [ @.recipients = ] 'recipients [ ; ...n ]' ]

[ , [ @.copy_recipients = ] 'copy_recipient [ ; ...n ]' ]

[ , [ @.blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]

[ , [ @.subject = ] 'subject' ]

[ , [ @.body = ] 'body' ]

[ , [ @.body_format = ] 'body_format' ]

[ , [ @.importance = ] 'importance' ]

[ , [ @.sensitivity = ] 'sensitivity' ]

[ , [ @.file_attachments = ] 'attachment [ ; ...n ]' ]

[ , [ @.query = ] 'query' ]

[ , [ @.execute_query_database = ] 'execute_query_database' ]

[ , [ @.attach_query_result_as_file = ] attach_query_result_as_file ]

[ , [ @.query_attachment_filename = ] query_attachment_filename ]

[ , [ @.query_result_header = ] query_result_header ]

[ , [ @.query_result_width = ] query_result_width ]

[ , [ @.query_result_separator = ] 'query_result_separator' ]

[ , [ @.exclude_query_output = ] exclude_query_output ]

[ , [ @.append_query_error = ] append_query_error ]

[ , [ @.query_no_truncate = ] query_no_truncate ]

[ , [ @.mailitem_id = ] mailitem_id ] [ OUTPUT ]|||

use outlook client on the same machine (as SQL box) and see if you have the same problem, if the you have the same issue using outlook then this an exchange issue, if outlook can send it OK, then you have the investigate the problem on SQL.

you also can set up DatabaseMail in SQL 2005 and test the send mail process then use following sp_send_dbmail within SSIS as T-SQL command.

MAIL

Ok, folks, a question for you. I wanna send email through MSSQL Server 2000
using SMTP protocol.HOW?Check out

http://www.sqldev.net/xp/xpsmtp.htm

John

"Licemjer" <licemjer@.licemjer.hr> wrote in message
news:bqq62m$op5$1@.ls219.htnet.hr...
> Ok, folks, a question for you. I wanna send email through MSSQL Server
2000
> using SMTP protocol.HOW?