Ok - not exactly. But there are a few things you need to configure on your SQL box if you want SQLMail to follow the rules of MAPI multithreading.
Before we get too deep, a couple notes:
- The articles mentioned below only discuss SQL 2000, but they apply to both SQL 2000 and 2005. I haven't checked, but they probably apply to 2008 as well.
- I know next to nothing about SQL or SQLMail. I just look at the crash dumps. So all questions about configuring SQL or setting up SQLMail beyond the specific recommendations I give below will be referred to /dev/null.
Avoiding crashes and hangs with SQLMail
- Set the MAPI_NO_MAIL_flag value in the registry. This tells SQLMail to pass MAPI_NO_MAIL in MAPIInitialize, which is needed if SQLMail is running as a service using an Exchange based profile. SQL 2000 needs the hotfix 329375 (or a later SP if there is one) applied in order to honor this setting. 2005 honors it out of the box. Here's the article for this setting: http://support.microsoft.com/kb/329375
- Set the MAPIInitializeFlags value to 10001 in the registry. This is MAPI_NT_SERVICE | MAPI_MULTITHREAD_NOTIFICATIONS. Note that for some reason you can't just put MAPI_NO_MAIL in this setting - both reg keys are required. This one is http://support.microsoft.com/kb/839405
- Never ever call xp_startmail or xp_stopmail. Never. Ever.
Let's discuss that last one a bit, since there's a lot of SQLMail KB articles out there that mention running xp_startmail as part of a workaround. The folks that wrote those articles just aren't aware of what SQL does when xp_startmail is run and the effect it has on MAPI.
The thing to understand is that SQL uses a lot of threads. There's a thread pool that sits around ready to service various jobs. When SQL is processing a script, as it gets to various commands it will grab a thread from the thread pool, have it execute the command, then return it to the pool. So in the course of processing a single script, SQL might use a number of different threads. Suppose you have a script that executes xp_startmail, xp_sendmail and xp_stopmail. Each of these procedures could get handed to a different thread!
Why is this bad? Because xp_startmail is essentially a call to MAPInitialize and xp_stopmail is a call to MAPIUninitialize. So by using this commands, we will end up initializing MAPI on one thread and uninitializing it on a totally different thread. And this would be bad - very bad - crossing the streams bad.
Fortunately, xp_sendmail will call MAPIInitialize/MAPIUninitialize on its own, so we don't need xp_startmail or xp_stopmail at all!
Every SQLMail crash and hang I've debugged was resolved by implementing the above recommendations.