Introduction

Back in the middle ages of development (say, late 1999), I had a requirement

to send out a complete report of shipments, receipts and on-hand inventory

every night to about 150 vendors that used our distribution center. Each

vendor could designate multiple recipients and we always included our

company's customer service representative when sending the report.

In total, we would send out something like 600 e-mails every night. We

initially tried using SQL Mail (integrating SQL Server 7.0 with Outlook) and

quickly found that this was not a very workable solution. We eventually

worked out a solution using a 3rd party DLL and a lot of custom VB

Scripting in a DTS package. We could process those 600 e-mails (each with

an attached report) in about 20 minutes using a 2-way server.

SQLAnswersMail would have been a handy utility to have had at the

time. For $179, SQLAnswersMail will permit you to generate e-mails from

stored procedures, DTS packages, custom VBScripts or .Net applications.

It will allow you attach results as a file or embed them in the text of the

message. Attachments can be formatted as html, PDF or comma-delimited

files. Recipient distribution lists can be defined in a query. If

you are already using SQL Mail (horrors!), SQLAnswersMail claims to implement

99% of SQL Mail functionality using the same parameters (so you can potentially

do a find and replace on xp_SendMail anywhere in your code).

SQLAnswersMail avoids using a MAPI client, instead opting for a thin SMTP

client interface. One nice feature that I really liked was the ability to

designate both a primary and a secondary SMTP server so that you can provide

for a measure of fault-tolerance in your bulk mailing process.

But I am getting ahead of myself here. Let's take a closer look

at SQLAnswersMail and what it can do for your operation.

Environment

I installed SQLAnswersMail in two environments. I first installed it on my

laptop (a Dell Latitude D505) running Windows XP Professional (SP2) and SQL 2000

Developer Edition (SP4). After working through the installation process and

verifying installation folders and registry entries, I then went to install it

on my home database server (an AMD Duron 1.0 GHz single processor server with 512MB

RAM). On this server I have installed Windows 2003 Standard Edition and SQL Server

2000 Developer Edition (SP4).

For e-mail connectivity, I did most of my testing in my home environment where I

have Exchange 2003 installed on a member server. Not wanting to incur the wrath of

my ISP for flooding their mail server with spam, I sent all test e-mails to internal

domain accounts.

Installation

SQLAnswersMail uses an MSI installer so it's easy to get

started. The installation process ends when a configuration window pops

up. The configuration utility is used to record the settings needed to

use SQLAnswersMail: the address of the mail server, connection settings for the

mail server and the database server and some other options. By default,

SQLAnswersMail will install a stored procedure (sp_SendSAM) in the master

database. This can optionally be created in another database, but I recommend

keeping the default so that it may be called from any database on the server.

Configuring the mail profile is very straightforward and the screen includes

a "Test" option to verify that you have the correct settings. As I mentioned

previously, there is an option to configure redundant mail servers. This is

a nice feature (which we had actually implemented previously in the scenario

I described above). You may configure as many mail profiles as you want; however,

you may only specify two mail profiles (a primary and an alternate) in the database

configuration properties window which is covered next.

Figure 1: Configuring the mail server

Configuring the database server is also fairly straightforward. You simply specify

the target server and the target database for the sp_SendSAM stored procedure.

The installation folder includes a stored procedure to remove the sp_SendSAM

procedure. I did experience one issue with permissions for the SQL Server service

account. The details of the issue are outlined below in the Support section.

Figure 2: Configuring the database components

The values for the configuration settings are stored in an INI file in the

installation folder. Using an INI file seems a bit archaic (since just about everyone

is using XML for config files these days). However, it does make the process of

manually editing the configuration setting very easy. Passwords that are stored

in the config file are encrypted.

SQLAnswersMail also installs a folder in your %ProgramFiles% directory.

In this directory are two executable files: SQLAnswersMail.exe and

SQLAnswersMailConfig.exe. SQLAnswersMail implements the "SqlAnswersMail.Message"

COM class used by the sp_SendSAM stored procedure. As mentioned previously, the

SQLAnswersMailConfig.exe is the GUI for the configuration utility.

In the installation directory there are also a couple of folders used to store

a cascading style sheet (for HTML documents) and an XML stylesheet for PDF

templates. More about these files later.

Figure 3: Installation Folder

Using SQLAnswersMail

I ran a number of different tests using the product. The documentation includes

a number of samples which made the testing process easy. The samples are mostly

geared towards the Northwind database which again greatly facilitates the process

of writing and testing samples. In short, the product is easy to learn and easy to

use. Perhaps the greatest complexity lies in the configuration of the stylesheets

for both html and PDF rendering. I have to confess that CSS and PDF XML templates

make my head swim (isn't that what web developers are for?). What I saw I liked very much;

there are two sample templates that are provided with the installation. These may be

customized and copied and there are input parameters for the sp_SendSAM procedure that allow

you to specify which template to apply at run time. It is possible therefore, to generate

distinctly formatted results based on user preferences that are specified at run time.

One of the nicest features is the ability to generate PDF documents and access them in

multiple ways. PDF documents can be attached to an email, accessed in the results

pane of Query Analyzer, or sent to an ADO Stream object on a web server (for which there

is sample code, but I did not test). Included in the documentation is a guide to

configuring the PDF style template to suit your output requirements. I did some

(very) minor tweaking with the PDF template and was able to generate some acceptable

looking reports. I'm confident that with additional work (and more expertise on my part)

professional quality reports can be generated.

I did not attempt a sustained performance test of SQLAnswersMail. I did simulate

sending out a product catalog to 91 customers using the Northwind database. I sent

out both an HTML embedded and PDF attachment sending the entire product catalog

to each "customer" (actually my internal domain e-mail account). Each process

took about 19 seconds (my Exchange server is a 5-year-old Dell XPS-T 500 with

256 MB RAM). I thought the performance was quite adequate given the hardware

limitations.

Support

The one issue I needed assistance with was during the initial configuration. I kept

getting an error that "Access isDenied" (see figure). I checked a number of different

things and finally sent an e-mail to request support. I was mulling the issue over in

my head while I went to get a soda from the machine down the hall. As I returned to my

desk a bright light went off in my head at about the same time that my inbox alert went

off: check the membership of the SQL Server service account.

Figure 4: Error message (service account with insufficient permissions).

In this case, I found that the service account was a member of the local Power

Users group, but not a member of the local Administrators group. Adding the service

account to the local Administrators group resolved the problem.

The response that I received by e-mail was prompt, courteous and provided

me with the essential clues I needed to solve the problem (even though I had

already solved it during the short period I waited for the response).

Conclusions

In the scenario I described in the opening section (where we had to send detailed

inventory and transaction reports to multiple customers), SQLAnswersMail would have

provided a great starting point. At the time, we could only provide a single format

for the reports (comma-delimited) and the reports all had to be attached to the e-mail

(whereas some customers would have preferred an html-embedded table for easier viewing).

We were unable to provide PDF documents for our customers (which would have been nice

in order to guarantee that no one changed the data either deliberately or by accident).

With SQLAnswersMail, we would still have been required to develop the framework of code

for determining which reports got mailed to which recipients. The time required to code

the formatting and attachment of data represented only about a quarter of the time

we needed for the whole project. Still, with SQLAnswersMail, we could have done much

more and achieved a greater look of "professionalism" than what we ended up with.

For $ 179, SQLAnswersMail would have been an easy sell to our management team.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the

best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.sqlanswers.com/Software/SAM/SqlAnswersMailOverview.aspx

Developer: SQLAnswers.com

Pricing:

Single Machine - US$179.99