xp_sendmail: failed with mail error 0x80040111

Hi guys,
Its been long since i posted any new stuff ...so far I've posted nothing related SQL 2000 but i thought this one is needed .

We recently faced a SQL Mail issue on WIN 2k Server .Anyone who has worked on SQL Mail issue in the past would be knowing how irritating this issue can be sometimes.But its actually our dis-interest that makes this issue difficult.Somehow we don't like such issues :) ...Since we resolved this issue and went through different permutations and combinations I would like to discuss this with you all so that in future we can save some time and resources on the same issue.This includes

-> Some very basic understanding on SQL mail .
-> What you should do/do not do when SQL Mail issue comes up.

Brief understanding on SQL Mail
--------------------------------
There are two ways you can send mails in SQL Server 2000 (Not talking about 2005 or 2008).

-> SQL Agent Mail
-> SQL Mail .

Yes , you heard it right .SQL Agent uses its own Extended M(ail)API session where as SQL Mail uses its own .However,Both SQL Mail and SQL Agent Mail connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server using Mail Profile. It is possible to use different profiles for SQL Mail and SQL Agent Mail at the same time.
Extended MAPI is nothing but a better version over Simple MAPI .Anyway, we don't have to go in this direction .

The SPs that we use in SQL Mail are :

xp_startmail <-- used to start the mail session
xp_sendmail <-- To send mail .This invokes sp_processmail.
xp_findnextmsg <--sp_processmail uses this SP
xp_readmail <-- used by sp_processmail
xp_deletemail<--deletes the message.
sp_processmail <-- processses the mail
xp_stopmail<-- Stops the mail session

We will concentrate on First two and the last one in the list above.You can read about these SPs under : http://msdn.microsoft.com/en-us/library/ms186841.aspx?PHPSESSID=lm71lj7i6gj5fjtebtb2srl4n3
This link is for sp_findnextmsg but has link to all other SPs and XPs.


The Issue that we faced.
-------------------------
One of the job was not sending the mail after completion .SQL Mail was connecting to Exchange server successfully before .
Outlook 2000 client was installed on the Win 2K server (and was working fine under SQL Server service account).
Although we concentrated mostly troubleshooting SQLMail it was SQL Agent mail issue actually .
But Since troubleshooting steps are same for both , we resolved the issue .

TroubleShooting done

-> Logged in to the Server > opened the QA and ran sp_stopmail .Success
-> Sp_startmail ....Took 10 mins to start and with error.
-> The error was :
Server: Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
-> We tried to send the test mail using xp_sendmail
xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
-> Assuming that the Profile might be corrupt we created nwe profile through control panel >> Mail option .
-> To our surprise we could not see that profile in the drop down list of both SQL Mail and Agent properties.
-> So we failed there as well .We have the new profile , but we cant use it .Another Big question ? WHY ?
-> Agent logs were showing this :[260] Unable to start mail session (reason: Unable to logon (reason: Unable to get the default MAPI Message Store due to MAPI
error 273: The information store could not be opened); check the mail profile and/or the SQLSERVERAGENT service startup account)
-> After wasting time on 18025 we concentrated on Error 0x80040111
-> Found KB 238119 (List of Extended MAPI numeric result codes)
-> found that the error means : MAPI_E_LOGON_FAILED
-> Now we were clear that this is an authentication issue.
-> SQL Agent mail was not able to talk to Exchange Server.
-> The exchange Engineer cleared that since both the servers can ping each other and we can use outlook web using the same account under Exchange is running , there is no isues with
Exchange Mail box .
-> He was pretty convincing.
-> Then we found that (Actually we already knew that )in the SQL Mail configuration it was mentioned that we need to make sure that in we are logged in to the server using the SQL Server
service account.
-> That is what we have not done and were not able to do actually (Since begening) because of some permission issues on that account while logging on to the Win 2k Server.
-> Since rest all trials were failing , we decided to force ourselves for one last time in resolving this issue after loggin gon tho the server using SQL Server domain account .
-> The Intel team helped us in doing that .
-> We logged on to the server >> created a new profile >> Changed the SQL Agent setting to point to that profile (which was now visible..wow).
-> Started the mail session using xp_startmail.
SQL Mail session started.
-> Sent a test mail : xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Mail sent.
-> That resolved the issue.

Do's to solve this issue
--------------------------
-> Ping the exchange server or the POP3 Server and make sure its a success.
-> Make sure that the Domain account for SQL Server(including agent) , Exchange and OS are same .
-> Stop the mail session first as it might be Stuck.
-> Start the mail session .If it works , fine .
-> If not then read the MAPI error and match it with the KB 238199 to understand the root cause .
-> In one of the situations when I was testing it on Lotus Notes to POP 3 account on Gmail I got 8004011d which means MAPI_E_FAILONEPROVIDER (due to slow network)
-> Check the Agent logs and make sure MAPI32.dll exists (Using MAPI32.DLL from C:\WINDOWS\SYSTEM32 (version 1.0.2536.0))
-> Make sure the mapistub.dll exists in C:\WINDOWS\SYSTEM32 folder
-> You can also test you MAPI profile : xp_test_mapi_profile 'hello'
-> Create the new profile and see if that helps .If it does it means that the previous profile was corrupt(We did not test this in our case and created a new profile straightaway).

Don'ts
---------
Never try to resolve this issue without logging on to the server with SQL Server service account .

Hope this helps .
let me know your comments ..