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:
Create a mailbox on the Exchange Server for the SQL Server login account.
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.
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.
Try using xp_sendmail. (not to worry if this fails, but
should work -- see the note above)
Create all alternate mailboxs on the Exchange Server that will send mail from the SQL Server.
Grant 'Send As' permission in the Delivery tab of each mailbox to the primary mailbox already tested. (Do this at the Exchange Server.)
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
Add the alternate mailboxs to the Profiles of the SQL Server's mail client.
Add the alternate mailboxes to the 'open additional mailboxs' dialog in the Exchange Server/Advanced tab of the mail client.
Test 'send as' to and from each mail box using the mail client.
Set the mail client to NOT start at system start up.
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