Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAnswers Review

By Hugh Scott,

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.

Ease of Use 5

Feature Set

5
Value 5
Technical Support 5
Lack of Bugs 4
Documentation 4
Performance n/a
Installation 4
Learning Curve 4

Overall

5

Product Information

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

Developer: SQLAnswers.com
Pricing:
Single Machine - US$179.99

Total article views: 7374 | Views in the last 30 days: 0
 
Related Articles
FORUM

Report Server Database Configuration

Report Server Database Configuration : Error

FORUM

Fresh install no database server

Fresh install no database server

FORUM

Help!!Configure Report Server issue:Database Setup

Configure Report Server issue:Database Setup

FORUM

Configuration Database

Database of how different SQL Server are configured

BLOG

SQL Server Silent unattended installation using configuration file

1) Open Installation Wizard Open Installation Wizard 2) Select Feature You want to install Select...

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones