Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steve123
Starting Member
8 Posts |
 Posted - 2007-09-19 : 16:16:12
|
| This is on sql server 2005: I created an agent job and I want to be able to notify people by email when it completes and when it fails. I first wanted to test the notification for when it completes. I created an operator and entered the E-mail name. In the job properties, I selected this operator in the Notifications page.The job runs an ETL process. When I run the job, after it completes,the email is not sent. This seems straight-forward. The email address is valid, I tested the email address and it does get sent when I do it from Outlook 2007.Anyone have this problem? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-19 : 16:24:50
|
| You have to have Database Mail configured properly, and it also has to be configured in the SQL Server Agent properties.CODO ERGO SUM |
 |
|
|
steve123
Starting Member
8 Posts |
Posted - 2007-09-19 : 16:54:12
|
| I configured database mail, and then sent a test email and that failed. The sql agent service was restarted prior to my test. Here is the error in teh Log File Viewer:The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-09-19T16:48:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it). |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-19 : 16:56:23
|
| OK, then you have to resolve the issues with Database Mail before agent job notification will work.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-19 : 23:29:44
|
| Did you point to valid smtp mail server on your network? |
|
|
|
steve123
Starting Member
8 Posts |
Posted - 2007-09-20 : 11:40:36
|
| Here's an update: I had to change the server name to a valid onein the Manage Existing Account dialog. I use Anonymous authentification (always had this selected). The test email now works, there is a menu item to do this when you right-click "Database Mail" under the Management folder in the database engine in Management Studio.However for my agent job, the email does not send on job completion.I also selected the Net send option in the job properties for "Whenthe job completes", and that works, but not the email. |
|
|
|
steve123
Starting Member
8 Posts |
Posted - 2007-09-20 : 12:16:16
|
| I now get the email to be sent, in the "SQL Server Agent" properties,Alert System page, had to check the "Enable fail-safe operator".Still I don't see how Alerts fit into this. I have some custom text I want to show up in the notification message. I add this text in the Alert properties Options page. |
|
|
|
steve123
Starting Member
8 Posts |
Posted - 2007-09-20 : 14:27:05
|
| I think now that the alert is used only for certain events, and not general message like with Operators. It looks like agent jobs can have only 1 notification (or Operator) chosen. To be more clear, the Operator I created to be used by the agent job was configured to send an email on the condition that the process completes - this was my critieria. However the alert on the other hand, is only for certain error #'s or events, a separate instance of notification. I don't have to use an alert with the agent job. |
|
|
|
|
|
|
|
|
Agent Jobs: Trouble sending Notifications/Alerts