xp_sendmail
-
Hi,
I have had a number of Jobs running for several months which use xp_sendmail to issue Emails. They have been working without problem. Until now
It seems that the routines are timing out. I have tried running xp_sendmail in its simplest form in QA but this times out with Timeout Expired, Communicationn Link failure.
If I look at a Job, Notifications, I can send a test email, so I must be getting access to an Exchange Server. On looking at Control Panel, Mail, Profiles I can see the profile I originally entered. On looking at SQL Agent properties I see that Mail Profile is blank. I can add the profile seen in Control Panel, but when I click Apply, OK and look at properties for a second time the Mail Profile is blank.
Am unable to use xp_stopmail, in order to restart the system, as I get an error that the system failed to stop the SQL Mail Session. It could be that one of our Jobs is currently trying to access the system and so xp_sendmail considers it in use and refuses to operate.
At the moment I can see no other option other than to restart SQL Server and if that fails to restart the actual server.
I am informed that NO changes/maintenance have been done to the server.
Any help mostwelcom. Thank you.
Colin
-
Is this on a clustered server ? (xp_sendmail is not supported on a cluster)
We've also had this kind of issues with xp_sendmail and are now using smtp-mail (check Gert Drapers site SQLDev.Net 
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This is a single, stand alone server. I recognise the benefits of smtp-mail and I am ttrying to get authorisationn to install it.
How did you solve the problem, before you switched to smtp?
Thanks
colin
-
fortunatly it happened on servers that could be rebooted at least every weekend, so if the problem occured, we planned a reboot asap.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If SQL Server 2000 looses connection with Exchange and tries to do a xp_sendmail during this time, the xp_sendmail interface can get disabled until SQL Server 2000 is restarted. There is a KB article that describes this behaviour.
We sometimes get this issue when work is done on Exchange. Our normal troubleshooting process is below. Most times the problem is fixed after 1), but sometimes 2) and even 3)+4) are needed. It is a long time since we needed to do any further steps, but back with SQL7 and NT4 a box rebuild was the only way we could get it working again.
1) Run xp_stopmail and xp_startmail, then check if mail is now OK
2) Stop & Start SQL Agent, then do 1)
3) Log on using the SQL service account, start Outlook and send an email. If this is OK go back to 1) otherwise we raise a problem ticket with our Exchange folks and wait until it is resolved. Often the ticket resolves to an Exchange or Firewall problem.
4) If 3) works but xp_sendmail does not, then we need to restart SQL Server
5) If all of the above have not fixed xp_sendmail, then delete and re-create the mail profile in Outlook, folowed by restarting SQL Server.
6) If mail functionality is still not working, consider rebuilding the box.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply