by Ross Mack - GUI Computing
There are few things I like more than receiving friendly email. So you can imagine how pleased I was when I discovered that it was actually possible to have Microsoft SQL Server not only send me email, but also to respond to emails that I send to the server. Isn't that nice ?
It turns out that this can also be actually useful.
SQL Server 6.0 was the first version to include the SQLMail extensions, but surprisingly few people seem to actually use this rather useful capability. Perhaps this is partly due to the fact that it is rather badly documented and there are some teething problems getting it to work if it's not configured correctly or if parts of your system were not installed in the correct order.
I have managed to configure SQLMail successfully (more through blind stubbornness than anything) and here are the traps and tricks I have discovered that are important for getting it configured correctly.
First of all you must provide a MAPI profile for your SQLServer to use when it logs on to Mail. Where can I start to explain this ? Essentially, when you first install and start using Exchange (or Outlook) on Win95, 98 or NT a profile is automatically created for you and hidden from your prying eyes (like you are a user or something). It is usually called something like 'MS Exchange Settings'. If you go into your Mail and Fax Control Panel Applet and click the 'Show Profiles...' button you will see this one entry in the list. Usually you do not see anything other than the default profile unless you want to configure the computer to enable the use of multiple profiles for different email services perhaps, or one for one the road and one for in the office, or just because different people may use the same PC. In most server situations you will probably not have profiles on the server for use by users (is that an oxymoron?) but you will need to configure such a profile for use by your SQL Server to log on to email. Keep in mind that your SQL Server will log on to email in (procedurally) much the same way as a normal user will.
The documentation for SQL Server suggests that if your server is configured to run as the LocalSystem account (a special account mainly used for service processes to authenticate on the server as) it should be able to pick up MAPI profiles configured for other usernames and use them. It even suggests that there is a groovy checkbox on the SQLMail configuration dialog to import such profiles into the user information for the LocalSystem account. If such a checkbox exists it has eluded my stringent examinations of the dialog in question.
|So, how do you actually get this to work?
Well, the easiest way I have found is to configure a specific UserName that the SQL Server service will log on as. This change can be made via SQL Enterprise Manager or more directly via the Services applet in Control Panel. You can then log on as this user yourself and configure an appropriate MAPI profile for that user (and therefore SQL Server) to use.
Of course the first step in doing that is to configure an account on your email system for the SQL Server to log in as. This generally doesn't need to be any different from a normal user account, so just configure it the same way. I have had SQLMail work with Exchange Server, an MSMail postoffice, and a POP3/SMTP server - as long as it works in Exchange it should work fine with SQLMail. No promises for things like cc:Mail though.
You need to make sure that when you start Exchange (by double clicking on your inbox icon) when logged on as that user that you will be able to connect directly to that profile. You must make sure that if service passwords are required (for connection to exchange server, MSMail postoffices, or whatever) that they are saved in the user's password list. Remember that when the SQL Server user starts up email [he|she|it|they] will not be able to interact with confirmation or login dialogs like a normal user (and I use the term advisedly) can. Once you have achieved this (and you may need to mess with this for a while depending on what sort of mail stores and delivery systems you are using) your SQL Server service should be able to connect quite happily.
You may be interested to know how to configure which MAPI profile your SQL Server should use when starting SQLMail. It's quite easy, simply go into Enterprise Manager and right click on the SQLMail service for the server that you are interested in. A menu will appear with a 'Configure...' item. Select this.
|You will notice the extensive dialog on the left, with a single text box and a small selection of buttons. Enter the profile name in the Text box and click on whichever button you feel is appropriate (the smart money is on 'OK'). It's probably a good idea to rename your profile from the default 'MS Exchange Settings', a singularly uninspiring name. A name like 'SQL MAPI Profile' might be more appropriate. Your mileage may vary.|
Now here is where a lot of people get stuck. It happens like this: you build your server, installing NT and SQL Server and so on, then you apply the NT service packs and the SQL Server service pack as you should and everythign is service packed to the hilt. You then decide to configure SQL Mail and realise that this requires that you install Exchange. Reluctantly you dig up the NT CD from beneath a topology diagram and some donut crumbs on your desk and you install the Windows Messaging components (Add/Remove Programs in the Control Panel).
Having followed all the excellent advice and hints earlier in this article and having everything configured just as it should be you expect to have SQL Server start up, start SQLMail and work straight away. Right? Wrong. If you installed the Windows Messaging stuff after NT Service Pack 3 you need to reinstall Service Pack 3. The reason for this is that the MAPI spooler (mapisp32, the magical little fellow that does the sending and fetching of messages) as it shipped with NT was not designed to run as a service. So, it just halts as soon as it begins to start up. No matter how you dangle carrots in front of it that puppy will not budge. NT Service Pack 3 updates the MAPI spooler to be safe to run as a service, it will then magically start to work.
The details of this problem can be found in a Microsoft knowledgebase article located at: http://support.microsoft.com/support/kb/articles/q159/4/25.asp
|If you want SQLMail to automatically respond to incoming email you need to use the SQLExecutive service to run the sp_processmail stored procedure regularly to do just that. You can configure this to run as frequently or as infrequently as you like. The more infrequent it is the less responsive SQlMail will seem to be, the more frequently you have it run the more processor time is being used to check for email. SQLMail does seem to run pretty quickly, however, so if you are serious about using it would probably not be a bad idea to have it run every 10 minutes or thereabouts.|
You will, of course, need to consider what overheads are involved in continual checks for mail. Will it involve a round trip to a remote POP3 server? Will it involve opening an MSMail postoffice on a clogged server across a slow link? In most cases these overheads will be trivial, but don't forget about them.
Once you have sp_processmail scheduled regularly you will be able to send your server an email with valid SQL statements in the body. For example you might send an email like this:
To: SQL Server From: Ray in Sales Subject: Who should I fire? SELECT FirstName, LastName, TotalSales FROM tblSalesPeople ORDER BY TotalSales DESC
A few minutes later you should receive an email back from the server with an attached text file with the resultant data in it. You can then decide to fire Lester. How cool is that?
One problem you may encounter here is that when the SQL Server tries to reply to the message it will use the display name it received on the incoming message, not the actual address of the person. For example, my email address is 'firstname.lastname@example.org', but it is displayed in email I send to people as 'Ross Mack'. SQL Server will therefore try to send it's reply to 'Ross Mack'. If you are sending via Exchange or an MSMail postoffice the mail system will probably be able to figure out what it means (it can map the display name back to the actual address), but an Internet mail address may be a different matter. One way around this problem is to add the addresses of people able to send email to the SQL Server to the SQL Server's own address book. If you can configure it to use some sort of central address book that's even better. You might also adjust the sp_processmail stored procedure (and, yes the documentation virtually encourages you to do that) to look up unknown email addresses in a database table.
In fact there is one change I would recommend you make to the sp_processmail stored procedure. When the SQL Server fails to send a reply to an incoming message it simply aborts and tries again next time it is scheduled to run. This is not all that handy. It is quite easy to make a couple modifications so that if the send fails the results are instead sent to an administrator address with some info attached as to what the problem was, and to remove the incoming mail from the inbox so the same error doesn't recur. At least this way the administrator is made aware of the problem straight away, can do something to rectify it, and also has the data that the user was after so that they can forward it to the correct recipient manually.
The revised code for this change is available for download.
If you look closely at sp_processmail you will note that the code is reasonably straightforward and uses a lot of useful other stored procedures that do little chunks of the real work. This allows you to customise the behaviour of your server quite easily. Also, the various stored procedures used for handling mail (including sp_processmail) are quite well documented in the Books Online (or those large, bound, papery objects that may be propping up a corner of your desk). I reccomend that you have a close look at what is said about both sp_processmail and sp_sendmail. sp_sendmail can be very easily used from within your own stored procedures to mail out warnings, information or whatever seems appropriate. Even periodical reports on server usage or some other important system data could be distributed that way.
Something I almost forgot to mention is that SQLExecutive also allows you to send email as the result of any scheduled task (as well as the ability to log such results in the Server Event Log), on success or failure or both. Once you have SQLMail configured this will just work, have a look at the options dialog for any given scheduled task.