lightweight mail subsystem

Tuesday Sep 7th 1999 by Bill Wunder

Need a bit of flexibility in sending mail from SQL Server? Want to be able to send from one of several mail boxes on demand? Have trouble with SQLMail? How about a email solution that simple, easy, and dependable.

Need a bit of flexibility in sending mail from SQL Server? Want to be able to send from one of several mail boxes on demand? Have trouble with SQLMail? How about a email solution that simple, easy, and dependable. (We're not talking about a SPAM engine here. This is a tool that will support a moderate volume of outgoing mail only. If you try to use it for SPAM, you'll get what you deserve.)

The lightweight mail subsystem is a MS SQL Server 7 utility that implements the MAPISEND.EXE utility from the Back Office Resource Kit (BORK) or from the Exchange 5.5 CD to provide highly flexible backend mail services for moderate capacity email requirements. In this column you can read about the SQL Server driver process, the associated mail client, and the file system configuration requirements. Hopefully you will see that this lightweight mail subsystem can be customized to a wide range of functional requirements .

About mid-life (SP3) SQL Server 6.5 started working nicely with an Exchange or Outlook Client. I was able to send from a half a dozen mailboxes using xp_stopmail, xp_startmail, and xp_sendmail. This enabled SQL Server to handle the logic to decide which mailbox to use: The SQL Server could send technical folks mail from a mailbox named after the server. Buyers could get confirmation email from the seller. Survey respondents could get email from the survey host. And best of all, all replies would come to the specified mailbox. SQL Mail was always idled to the server named mailbox: ready to send out that technical info!

This scheme has proven to be unreliable with SQL Server 7.0. When I tested it the SQL Mail/MAPI Spooler connection would hang and drastic measures were needed to get the machine back to normal. It really does seem to be a confusion problem when the mailbox context changes. At any rate, I decided I didn't have enough confidence in that solution to take it to production post upgrade. I had played with the MAPISEND.EXE in the BORK and was quite impressed with the small size and features of the utility. It became a candidate for a prototype.

I Set up a few tests and was pleased to discover that as long as I called a seperate physical copy of the MAPISEND utility, each of my few users could send mail simultaneously. I sat down and designed a simple architecture dubbed the 'lightweight mail subsystem' that would act as a SQL Server interface to MAPISEND.

Take a look at the MAPISEND usage information or plow right in to the lightweight mail subsystem.


Mapisend.exe is avaialable in the BORK. Get the BORK and review the documentation for MAPISEND.exe. You'll need to follow the instructions there to properly configure the system to support the utility.

As you can see below, the tool is quite small:

C:\BORK\Exchange>dir mapisend.exe
      Volume in drive C has no label.
      Volume Serial Number is CC15-AF61

      Directory of C:\BORK\Exchange

      04/16/97  12:00a                13,584 MAPISEND.EXE
                    1 File(s)         13,584 bytes
                                 892,027,392 bytes free

And the tool is reasonably flexible if you can get all the switches to work for you. I'll have to admidt to some difficulty when specifying multiple recipients and when specifying even one recipient in the cc list. Everything else seems to be as advertised.

     Send MAPI messages from Win32 command line.
     Usage: mapisend -u -p -i -r [-c][-s][-m][-t][-f][-v][-?]

     -u  profile name (user mailbox) of sender
     -p  login password
     -i  interactive login (prompts for profile and password)
     -r  recipient(s) (multiples must be separated by ';' and
         must not be ambiguous in default address book.)
     -c  specifies mail copy list (cc: list)
     -s  subject line
     -m  specifies contents of the mail message
     -t  specifies text file for contents of the mail message
     -f  path and file name(s) to attach to message
     -v  generates verbose output
     -?  prints this message


lightweight mail subsystem

the lightweight mail subsystem


Note: The utility presented here is as simple as possible. The intention is that you take the utility to the level you need for you solution. Customize it. Modify It. Integrate it. What ever you need to do to put it in service for your environment is OK!

This template lightweight mail subsystem is fed from a single table: messages. This table also serves as the sent folder for all mail. You can create a separate database for the utility or you can build it in an existing database. You can add detail and normalize this to whatever detail you want. For example, you may want to store details other than email address about recipients. Then it would make sense to have a recipients table and a messages table.

There are two procedures that send mail from the table. The first procedure, sendMAPIMail sends an email for a specified row in messages using MAPISEND.exe. A second procedure, SendMail is used to send one email account via xp_sendmail and all others via MAPISEND. Messages belonging to all other than the xp_sendmail profile are routed to the sendMAPIMail procedure. An important difference is that SendMail will attemp to send an email for every row where the sent status is 0 while sendMAPIMail only sends one email (by messages.id) each time it is called irregardless of sent status. It is suggested that you use both procedures and invoke SendMail from the scheduler to send messages. (Review the code in sendMail to see how this works. For example, you could default the @mailbox parm of the sendMail stored procedure to a value that will never occur in the messages table sender column if you do not choose to have an account using xp_sendmail.)

It's up to you to determine the best way to populate the messages table for your needs. One reason to use xp_sendmail would be to run a query repeatedly. You can put the query in a row in messages and simply reset the sent status to 0 whenever you want the report to be run and sent. A heterogeneous query does not seem to want to run from xp_sendmail. You can also insert into messages from a linked server, thus centralizing the mail sent from all SQL Servers. If you want to run queries where the results are sent from an account using MAPISEND, you'll can write a procedure to populate messages properly and let sendMail take it from there. There are many possibilities.

Configure the mail client as follows:

  1. Create a mailbox on the Exchange Server for the SQL Server login account.
  2. Set up the SQL Server's mail client to use this mailbox. (Not SQLMAIL, you just need to set up the Outlook or Exchange or whatever MAPI compliant mail client you will be using.) Test by sending a message from the mail client.
  3. Set this account as the SQL Agent and SQL MAIL mail profile and use the TEST button for both. You should get a success message. Don't go any father until you do if you want to be able to send from one account via xp_sendmail, SQLMail, and the SQL Agent.
  4. Try using xp_sendmail. (not to worry if this fails, but should work -- see the note above)
  5. Create all alternate mailboxs on the Exchange Server that will send mail from the SQL Server.
  6. Grant 'Send As' permission in the Delivery tab of each mailbox to the primary mailbox already tested. (Do this at the Exchange Server.)
  7. Setup the directory structure for these accounts. (The programs default to c:\mailbox\) Make sure the MAPISEND.EXE is located in the c:\mailbox folder. You need to add a subfolder named the same as each mail account that will use MAPISEND.EXE.
  8. Add the alternate mailboxs to the Profiles of the SQL Server's mail client.
  9. Add the alternate mailboxes to the 'open additional mailboxs' dialog in the Exchange Server/Advanced tab of the mail client.
  10. Test 'send as' to and from each mail box using the mail client.
  11. Set the mail client to NOT start at system start up.
  12. Test.
  13. Restart the server and retest if necessary. Sometimes SQLMail gets flakey if you get it in a bad state.
At this point you should have a functional lightweight mail subsystem.
Mobile Site | Full Site