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.
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
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
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
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).
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.
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.
|Ease of Use
|Lack of Bugs
Web Site: http://www.sqlanswers.com/Software/SAM/SqlAnswersMailOverview.aspx
Single Machine - US$179.99