I'm not sure I'm posting to the correct thread, but here it is anyway.
We have fired up Database Mail on our 2005 SQL Server and all seems to work on the test side. You can Right Click Database Mail and say Send Test Email and as long as you put in the entire email address i.e. email@example.com the email comes back just fine.
The issue is that when I create a simple job that has one step which is to select from a table that doesn't exist, I cannot get a notification to be sent via email on the failure of the job.
Here is the message I get from the job history:
The last step to run was step 1 (exec syntax error). NOTE: Failed to notify 'MyID' via email.
Here is what is listed in the EventLog:
2006-03-07 12:08:48.543 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-03-07T12:08:48). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail addre
I'm not sure where to go.
Is it something to do with a service account?
We have Database Mail selected in the Alert System under SQL Server Agent. Although the TEST button is dimmed out when Database Mail is selected. I haven't changed it to SQLmail, but if you selec SQLmail from that dropdown the TEST button comes to life.
We want to use Database Mail, so anyone have the answer?
Thanks for the response. I logged in as the sqlserviceagent this morning and set up the Database Mail from the server, then restarted the sql agent and for the most part it's now working.
The only part that's not working (and maybe it doesn't work this way), but the names in the operator property email address field do not resolve. You have to type in a fully qualified email address or multiples for it to send out emails.
So, the question now is How do I get it to resolve the names (i.e. look up the names when creating a list of emails for an operator), or does it do that as an option?
Please refer http://msdn2.microsoft.com/en-us/library/ms186358(d=ide).aspx.
To set up SQL Server Agent Mail to use Database Mail
In Object Explorer, expand a server.
Right-click SQL Server Agent, and then click Properties.
Click Alert System.
Select Enable Mail Profile.
In the Mail system list, choose Database Mail.
In the Mail profile list, select a mail profile for Database Mail.
So, What was the fix for this? I am seeing the same issue here. The test mail goes fine but the actual job doesn't notify the job status. Before running the job, the message shows "DatabaseMail process is started". When I run the job, the log shows "DatabaseMail process is shutting down". Any clue?
oh..okay..I missed the following -
7. Restart the SQL Server Agent