Getting SQL Server to send mail has never been easy. Whether it is sending an e-mail to a mailing list, or sending the results of queries to users, SQL Server doesn’t make this task easy. And if you wanted to output anything other than simple text, you were in even deeper trouble. But not anymore. SQLAnswersMail, from SqlAnswers.com, has created a simple SQL Server add-on that makes it easy to send e-mail from SQL Server in text, HTML, or PDF. And very importantly, it is SMTP-based, not requiring MAPI to run.
Version and Configuration
This review was done using the following software and hardware configuration:
- Operating System: Windows 2000 Advance Server Service Pack 4
- SQL Server: Version 2000 Service Pack 4
- Processor: Intel Pentium CPU 2.80 GHz
- Memory: 512 MB
System Requirements
- Microsoft Windows 2000 or 2003 Server (all editions)
- Microsoft SQL Server 2000 (all service packs)
- MDAC 2.5 or later
- (MDAC 2.5 is preinstalled with Windows 2000. SqlAnswersMail works with later versions of MDAC as well.)
- CDONTS or CDOEX (CDONTS ships with Windows 2000. If your computer has the MAPI client installed (Outlook) then CDOEX libraries will be used instead of CDONTS.) MAPI is not required.
- SqlAnswersMail must be installed with an account that has administrative privileges on the Windows server.
Product Overview
SqlAnswersMail provides all the robust features of xp_sendmail, such as sending attachments from SQL queries, yet it doesn’t have any of the xp_sendmail disadvantages that come from its reliance on MAPI and Microsoft Outlook. In addition, it not only supports text, it also supports the creation and sending of HTML and PDF documents.
To learn a little about how SQLAnswersMail works, let’s begin by taking a look at the SqlAnswersMail configuration tool, which is used to set up the necessary mail configuration. The configuration screen has two tabs. The first is used to set up the SMTP server, and the other one is to set up SQL Server to work with SqlAnswersMail.
Setting up SMTP on your server is very easy, as you can see from the following screen. All you have to do is specify the name of the Mail Server, sender’s e-mail address, default domain, and authentication method.
The SQL Servers tab (shown below), is used to configure SQL Server for use with SqlAnswersMail. You can configure SqlAnswersMail to use one mail server as a primary server and another mail server as a backup server. This way, if the primary server isn’t available, SqlAnswersMail attempts to deliver mail via the backup server.
From this window you can also select the stylesheet for HTML-based e-mail (if used), the mail format (whether it is text or HTML), and the type of database connection to be used.
Implementation
Once SqlAnswerMail has been configured, you are ready to use it. Essentially, all you have to do to send mail is to use the sp_SendSAM stored procedure, which was added to SQL Server when SqlAnswerMail was installed.
For example, the following query will generate an email in the HTML format.
exec sp_SendSAM
@subject=’List of Category’,
@recipients=’dineshasanka@gmail.com’,
@format=’HTML’,
@message=’This is a list of the categoryies that are available’,
@query= select top 4 * from Northwind..Categories
Following is the portion of a mail which was received from SqlAnswersMail.
