August 7, 2002 at 6:43 am
OK Guys!
I have a stack of automated jobs.
For months now they've all been running fine.
Then about a week ago - bang - Sometimes Notification mails are not being sent.
The Jobs complete OK, but looking at the Job history there is a footnote:
NOTE: Failed to notify 'MyTeam' via email.
The operations guys are scratching their heads claiming no new s/w, patches or upgrades have been applied to any of the servers.
Has anybody got any clues ?
Regards
Andy P
Andy P
August 7, 2002 at 6:54 am
What version and service packs are you on? There are a gob of mail related problems with SQL 2000. Best to get on SP2 straight away.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314309
August 7, 2002 at 7:02 am
We have a single mail account (sqlmail) and there are a whole heap of servers, I don't know if the 2000 boxes are affected but there are 2 sql-svr 7 boxes that are?
Andy P
Andy P
August 7, 2002 at 7:15 am
Andy,
Dont know if this is the cause but Q.13 and Q.14 of this article were the cause of a simelar issue I had recently
Tim
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q315886
August 7, 2002 at 7:15 am
Slight ammendment to my last.
The sql 2000 boxes don't have exhange server on them yet so there won't be an issue there (YET?). It's just the 2 sql-svr 7.0 boxes affected.
Andy P
Andy P
August 7, 2002 at 7:22 am
Thanks for that Tim,
Checked the doc. But still no help there.
One of my jobs has an xp_sendmail call as one of the tasks, that always works, and even though the notification for the job hasn't been sent the subsequent jobs all run fine. The agent has been stopped and restarted, that didn't make any difference.
I do have a work around but that means modifying the DTS packages to mail explicit personnel, but I'd sooner keep it at job notification to operator level !
Regards
Andy P
Andy P
August 7, 2002 at 8:17 am
We've a gradual worsening over time with these issues and it appeared to be that SQL Server wasn't always able to make a connection to the Exchange Server, especially during times of heavy network activity (like system backups).
What we did here was create a set of Personal Folders for the profile and explicitly add the operators' email addresses to the Contacts list. Delivery was changed to the Personal Folders and the Contacts list was where addresses were looked for first.
What this did was allow for the message to sit in the outbox until the connection could be made and also prevent any failures due to "could not resolve recipient."
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 7, 2002 at 9:05 am
Thanks for that Brian.
I have passed that on to a colleague who can see if our operations guys can do this.
Regards
Andy P
Andy P
August 9, 2002 at 1:48 pm
We have that problem on the first xp_sendmail after the mail server is rebooted, then it's fine for subsequent xp_sendmails.
August 13, 2002 at 7:46 am
We don't have a problem with xp_sendmail (yet and touch wood). But the notifications are continuing to fail, even more are not getting sent now. The exhange settings possibility (from brian) were passed onto colleagues but nothing has been decided on whether or not they will even try it yet !!!
Andy P
August 13, 2002 at 1:17 pm
The one thing about using Personal Folders is the email will appear successful to SQL server, because the mail was created. The MAPI client will then continue to try and deliver until it can do so successfully. That's the reason why they switched to this method where I work.
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley
August 15, 2002 at 6:30 am
It's the system guys! Don't start adding variables such as patches, etc. but rather did deeper as to what environmental system changes have occurred just before it stopped working properly. Software systems just don't stop working on their own. Remember Newton's 3rd Law of software motion: a software system working properly will continue to work properly unless some moron alters something that affects the software or it's environment.
Good Luck,
Ed Lyons
August 15, 2002 at 7:06 am
I keep reading about these complex methods DBAs are designing for SQL Mail on multiple servers.
Here is what we do in order to keep it simple and keep the complexity out of it.
Designate one server as your SQL Mail server. This server has all of your operators and alerts on it. Make sure SQL Mail works fine on that server.
Have all other SQL Servers do event forwarding (property tabe of SQL Server Agent) to the SQL Mail server.
This way you only need to keep a single server configured and you can manage all alerts and operators from one location.
Have all RaisError statements using WITH LOG at the end of them.
If you have any questions on how this works, email me at jmorr2@eckerd.com
August 15, 2002 at 7:19 am
andyhpoole wrote:
The sql 2000 boxes don't have exhange server on them yet so there won't be an issue there (YET?).
Andy P
--------------------------------
I don't mean to second guess you or anything, but why would you install Exchange Server on your SQL Server boxes? That is not required at all in order to make SQL Mail work.
August 15, 2002 at 3:59 pm
quote:
This sounds like the problem we had when we upgraded from 7.0 to 2000 SP2. After much research, I found posts on Microsoft and on SWYNK.com where users said when they contacted Microsoft support with the problem they were told to replace SQLMAP70.DLL from SP2 with the one from SP1 (the SP1 dll is version 2000.080.0384.00 - ours was in the BINN directory). You should restart SQL once you've replaced the dll.
Edited by - david morris on 08/16/2002 10:00:36 AM
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply