SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mail - test is OK, but jobs do not notify


Database Mail - test is OK, but jobs do not notify

Author
Message
SQLmannn
SQLmannn
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 61

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. me@yourserver.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,





Jim Dillon-291542
Jim Dillon-291542
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 176
It seems as though you don't have Database mail set up correctly.

The error message points to an invalid email address and or username / password for the email account. You might verify that you have the profile and account configured correctly.

If the profile and account are configured correctly try restarting the SQL Agent.
(That worked for my problem, but I wasn't getting exactly the same errors you are, I was getting: "Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter."

Thanks,
jim
SQLmannn
SQLmannn
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 61

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?

Thanks, tony





Jim Dillon-291542
Jim Dillon-291542
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 176
I do believe that you have to put the actual email address in that field, not a name for the server to resolve.
I have multiple operators set up, so I specifiy that all of my (3) operators are sent emails. That way I don't have to type multiple email addresses in that particular field
GeoPat
GeoPat
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 34

Please refer http://msdn2.microsoft.com/en-us/library/ms186358(d=ide).aspx.

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.

  2. Right-click SQL Server Agent, and then click Properties.

  3. Click Alert System.

  4. Select Enable Mail Profile.

  5. In the Mail system list, choose Database Mail.

  6. In the Mail profile list, select a mail profile for Database Mail.





PM-475853
PM-475853
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 16

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?

Thanks


PM-475853
PM-475853
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 16

oh..okay..I missed the following -

7. Restart the SQL Server Agent

It worked.

Thanks


sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)

Group: General Forum Members
Points: 861 Visits: 1273
Is there anyway to just test the operator(s)?

¤ §unshine ¤
raul.pino
raul.pino
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 1
In the object explorer, go to ...

Management>>
>>Database Email (right click)
>>>>Click on "Configure Database Mail".
>>>>>>Select "View or change system parameters"
>>>>>>>>The "Database Mail Executable Lifetime (seconds)" is set at 600 seconds by default.
(600 seconds / 60 second in a minute = 10 minutes)

If you look at the Database Mail logs, you can see the service shuts down after ten minutes. I set this value at 43200 seconds which is equivalent to 24 hours.

After changing this, I have never had another problem.

To start the database mail after you have changed the settings, just send a test email by right-clicking on Database Email in the Management tab. When the test email is sent, it will auto-start the service. Keep on eye on the log for after ten minutes to ensure the service does not shut down again.
raajeshvaradarajan
raajeshvaradarajan
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13
Hi,

Just wanted to point out that 43200 is 12 hrs not 24 hours; in case it has not been pointed out.

raul.pino (2/3/2011)

In the object explorer, go to ...

Management>>
>>Database Email (right click)
>>>>Click on "Configure Database Mail".
>>>>>>Select "View or change system parameters"
>>>>>>>>The "Database Mail Executable Lifetime (seconds)" is set at 600 seconds by default.
(600 seconds / 60 second in a minute = 10 minutes)

If you look at the Database Mail logs, you can see the service shuts down after ten minutes. I set this value at 43200 seconds which is equivalent to 24 hours.

After changing this, I have never had another problem.

To start the database mail after you have changed the settings, just send a test email by right-clicking on Database Email in the Management tab. When the test email is sent, it will auto-start the service. Keep on eye on the log for after ten minutes to ensure the service does not shut down again.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search