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.