May 8, 2002 at 9:04 am
Hello,
I have a job scheduled to run a stored procedure that contains the xp_sendmail command. I received this error today:
xp_sendmail: failed with mail error 0x80040115 [SQLSTATE 42000] (Error 18025). The step failed.
I connected to the server via QA and ran the same xp_sendmail command. I received the email just fine. So, I don't know why the command failed during the job. I searched in Microsoft's KB for all keywords but came up empty-handed. Does anyone have any suggestions as to how I can discover the cause? And / or fix this issue?
Thank you very much,
Melanie
May 8, 2002 at 9:37 am
Not sure, this should be functioning the same. Can you create a new job that only does xp_sendmail and see what happens?
Steve Jones
May 8, 2002 at 9:38 am
Check an article xp_sendmail Using a Microsoft Exchange 2000 Mailbox Fails with Mail Error 0x80004005 or 0x80007005 (Q293422)
http://support.microsoft.com/default.aspx?scid=kb;EN;q293422
May 8, 2002 at 3:23 pm
Hello,
Thank you for your replies. I guess I should have mentioned that the job has run successfully for four days. I ran it again after it failed and the job ran successfully.
Could it possibly mean that the server was just busy at the time the xp_sendmail command was issued? I really want to understand why the error even occurred.
Thank you.
May 8, 2002 at 10:41 pm
Not sure. It's an add on and not as stable as the rest of the product. I've had some random errors with it I can't explain as well.
Steve Jones
May 9, 2002 at 6:01 am
Melanie,
After having xp_sendmail cause many important jobs (either procedure based or DTS package based) to fail, I have moved to a homegrown way of using xp_sendmail(there are others I am sure).
I create a table called Notifications and then have a procedure I created populate that table. In all my stored procs and DTS packages that require mail I use this new procedure to populate the table. Then I have one job that looks at the Notifications table and runs xp_sendmail against the records.
The best thing about this is that the procedure that inserts into this table never fails because it really is just an insert and if xp_sendmail fails when processing the records it is not causing any problems with data moves, updates, etc.
It may seem somewhat odd but it has really cut down on my headaches.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 12, 2003 at 8:59 am
I'm doing something similar to this but have recently experienced problems. See my forum entry: http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=9937
It use to work but now the DTS package stops as soon as xp_sendmail within the called stored procedure encounters an invalid email account.
Myles
quote:
Melanie,After having xp_sendmail cause many important jobs (either procedure based or DTS package based) to fail, I have moved to a homegrown way of using xp_sendmail(there are others I am sure).
I create a table called Notifications and then have a procedure I created populate that table. In all my stored procs and DTS packages that require mail I use this new procedure to populate the table. Then I have one job that looks at the Notifications table and runs xp_sendmail against the records.
The best thing about this is that the procedure that inserts into this table never fails because it really is just an insert and if xp_sendmail fails when processing the records it is not causing any problems with data moves, updates, etc.
It may seem somewhat odd but it has really cut down on my headaches.
Hope this helps.
David
February 12, 2003 at 12:19 pm
One thing that I've experianced at times (but not always) is that SQL Mail fails if the Exchange Server was restarted, while the SQL Agent was running.
I try to make a habbit of stopping and starting the agents after an Exchange reboot has occured.
February 12, 2003 at 5:02 pm
We're also encountering similar problems with xp_sendmail on a random basis. Our problem is compounded because we have several jobs and triggers that run with xp_sendmail embedded in them and when one fails it locks the rest of them up. These processes show up as active processes but we are unable to kill them. The only solution is to restart our server.
This problem started showing up about a month and a half ago and we had not made any changes to our SQL boxes prior to this problem. We're also still running Exchange 5.5. Since then, we've applied SQL SP3 and still encounter the problem every now and then.
One of our workarounds was to pull a SMTP script off of SWYNK.Com's SQL script library. However, I'd still be interest in knowing what the true problem is.
February 12, 2003 at 6:40 pm
We have had the same issue. We have found that killing the MAPI process will usually clean up the server enough so that we do not need to reboot.
February 13, 2003 at 2:41 am
We too have this problem. If we rebooted the server every time the problem occurred, I think we would have to reboot every day!
I have considered a solution similar to that proposed by DavidB, but am also considering using an external program to generate the mail messages.
Now if only I had the time to do either of these!
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 13, 2003 at 5:14 am
I have found a solution to the invalid email problem, just make sure you enter all your email addresses in internet format with the '@' symbol (eg. xxxx@yyy.com), rather than as an exchange mailbox format. If an address is invalid, Outlook will presumably send to that address since it has no means to verify if it is correct, and it doesn't stop your procedures from running.
Regarding the xp_sendmail being unreliable, I have a job that I have had to put a retry into to make it work, as it routinely fails on the first attempt. I have another where the same step is repeated since xp_sendmail fails first time without any error, yet always works on the second attempt. Yet I have other places where it seems to work flawlessly!
February 13, 2003 at 10:20 am
I too found that xp_sendmail failed from a scheduled job, worked when run interactviely. I connected to the server with terminal services (like being at the console), and recreated the job step. No problem since. I think it may be permission related.
February 14, 2003 at 12:38 am
I too have an xp_sendmail problem. This occurs on a w2k-sp2 clustered server (i know mail is not supported on a cluster, but anyway) Every once in a while it gives these errors and stops working through sqlagent :
"Executed as user: SQLAgentADDomain\Account. SQL Mail session started. [SQLSTATE 01000] (Message 17954) xp_sendmail: Unable to login to SQL Server. [SQLSTATE 42000] (Error 17969) ODBC error 11 (08001) General network error. Check your network documentation. [SQLSTATE 01000] (Error 18014) ODBC error 1 (01000) ConnectionOpen (ConnectionOpen()()). [SQLSTATE 01000] (Error 18014). The step failed."
When I execute the same as done in the job, it works fine.
Stop/start of sqlagent does not help, no mapiservice hangs out there.
Any hints ?
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
February 14, 2003 at 8:25 am
There is a MS bug in xp_sendmail, the bad mapi.dll in sql2000/sp2. We have the same problem, called MS and they sent us this updated .dll (should be version 603). MS is telling that this was good in sp1, but damaged in sp2 and fixed in sp3, but not 100% sure.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply