October 15, 2002 at 1:44 pm
Hi everyone, my script that contains xp_sendmail stopped working over the weekend with the following error:
xp_sendmail: failed with mail error 0x80040115 [SQLSTATE 42000] (Error 18025). The step failed.
I went to EM and right clickec on xp_sendmail and clicked on the Test button and the test was successful. Any idea what might be wrong?
October 15, 2002 at 4:53 pm
Can you post what does the job does?
October 15, 2002 at 8:01 pm
The job invokes the main procedure which in turn calls other procedures. The last procedure sends out a mail with the result of the process. This used to work until this weekend. Nothing (as far as I can tell/know) has changed. Thanks.
October 23, 2002 at 2:51 pm
shahgols,
Did you find out what was the prooblem? I am having similar error. Wonder if it's the same reason. Thanks.
Msg 17906, Sev 18: Access denied [SQLSTATE 42000]
October 24, 2002 at 9:38 am
What Service Pack are you on? Make sure it's the latest. SQL2k is extremely buggy in the mail area (even after SP2). Not sure if this helps but I had similar problems with xp_sendmail after our upgrade to 2000.
Also, I ran across this on Google:
http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=7298
October 24, 2002 at 10:48 am
Thanks, jayharper.
We are actually SQL 7.0. ANy suggestions for that? Thanks.
October 24, 2002 at 6:08 pm
I stopped and started the sql server service and that fixed it. It happened again later though. I think the latest sql server patch (is the latest patch 2?) fixes some issues with xp_sendmail, apply those and hopefully it will fix your problem. I got my error, even though I had already applied the patch.
October 25, 2002 at 10:34 am
Rebooting that machine worked for me. Thanks guys.
October 28, 2002 at 12:50 am
I have the same problem on a server on no default instance. Sometimes MSSQL server loose the Mail Profile. On the same W2K server, I have a default instance with no sqlmail problem.
October 28, 2002 at 3:52 am
I used to have problems with SQL Mail on SQL Server 7 failing every few weeks for no reason. (There may be a buffer filling up somewhere, but this is unconfirmed.) I found I could resolve this by stopping and restarting SQL Mail using xp_stopmail and xp_startmail.
If your script is occasionally run or execution speed is not critical then this can be done before you execute xp_sendmail, as this takes a moment. However if execution speed is more critical (my mail was fired from a trigger), I'd recomend scheduling a job to run once a day or so to stop/start SQL Mail. This made SQL Mail much more stable on my server.
We have now upgraded to SQL 2K which seems to be better, as this job is no longer required, even if it does force you to load Outlook 2000 on your server.
Edited by - ed harling on 10/28/2002 03:54:07 AM
Edited by - ed harling on 10/28/2002 03:54:58 AM
October 28, 2002 at 6:44 am
I have this problem on my SQL 2k SP2 with all post SP2 hot fixes applied.
Problem became very prevalent when we were upgrading our mail servers from Exchange 5.5 to 2000, which was a nightmare might I add. I could tell you every time they rebooted the mail server because my sql agent mail would fail, though the sql service’s mail account continued to work. Once I stopped and started the mail service everything would return to normal, until they rebooted again.
From what I can tell the problem appears to be linked to the sql agents mail profile and its in-ability to retry a connection to the mail server once it receives an error response, either a time out, access denied, etc. Maybe they had the intern write this piece of code:)
Now that the kinks are worked out with the mail server upgrades I haven’t had any issues since, nor have we rebooted the mail servers since.
October 28, 2002 at 11:16 am
I have had a similar problem on SQL 7 (sp2) running on NT 4. The xp_sendmail would stop working. Stopping and starting SQL mail does not work in our case. We usually reboot the server when this problem happens.
Since we are slowly migrating to SQL 2K, we are not worried about solving this problem for SQL 7.
We have also decided to have a local SQL box handle our failure notifications for all servers using each server's sysjobhistory table. We are going to modify the 'usp_failed_jobs_report' script created by Greg Larsen to handle the notifications.
If a problem happens with SQL Mail, we will be able to reboot the monitoring box without any impact on a production system.
October 28, 2002 at 11:23 am
samiu, I am interested in learning more about the monitoring server and how that works. Where can I get more info on that and the script written by Greg? Thanks.
October 28, 2002 at 2:31 pm
We have had problems with xp_sendmail ever since SQL6.5. It appears SQL is very poor at handling email error/connection retries. We tried it with both Exchange and SMTP profiles and had better luck using an Exchange profile. The best combination we have found is SQL2000 with SPK1, Outlook 2000 SPK1 and Exchange. SQL2000 SPK2 caused SQL mail to frequently hang again. Another possibility is to use CDO. Look at MS Knowledge Base article Q312839 for an example on how to bypass xp_sendmail.
October 28, 2002 at 4:30 pm
The script from Greg Larsen is available from SQLServerCentral.com. The address is:
http://qa.sqlservercentral.com/columnists/glarsen/failed_jobs.asp
The purpose of our monitoring server is to have 1 server that will provide the status of other servers. By having a separate server, we will not impact any production server. Since we have about 12 servers that we monitor, it became a logistical problem to copy our monitoring scripts to each server. We have also had SQL Server Agent Stop and fail to restart on several servers, which would negate our monitoring until we noticed that the Agent was stopped.
Our solution was to use a development server that we have to run the monitoring scripts and monitor all of the other database servers. We are changing our scripts to make use of linked servers. This will allow us to have one copy of the scripts yet monitor all of the servers.
For our needs, we are trying a central monitoring server using SQL scripts. If we get budget approval, we would prefer to buy a true monitoring tool and monitor SQL and the Operating System, but until then, this is our solution.
I hope that helps you.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply