SQLMail Hates MAPI

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

  1. 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
  2. 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
  3. 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.

Comments (2)

  1. Euan Garden says:

    Your comment about 3 is not strictly correct. The reason that recomendations did exist to run start and stop mail was because in SQL 7 SQLMAIL used Simple MAPI running on a dedicated background thread. In SQL 2000 this was no longer supported in the XP architecture and hence SQL was forced to switch to using Extended MAPI and due to the changes in the way UMS and XPs interact, calling start and stop was no longer needed.

  2. Ok – it’s still correct for SQL 2000 and above – which is what I was talking about. And there are plenty of articles still recommending those functions for SQL 2000. Thanks for the insight about SQL 7 though.

Skip to main content