You need to configure SqlAnswersMail before it can be called from your sql scripts.
Configuration settings
are stored in SqlAnswersMail.ini, which is located in C:\Program Files\SQLAnswers\Mail by default. You can configure this INI file
with the SqlAnswersMail Configuration utility we have
provided. SqlAnswersMail Configuration starts
automatically at the end of the installation process, or you can start it on
demand from the Windows Start menu.
SqlAnswersMail Configuration has three tabs:
Mail Servers, Sql Servers, and Queues. You first must
configure one or more mail servers, and then you can configure sql server instances to use with one of the configured mail
servers. Configuring queues is necessary only when you want to have one or more
pre-scheduled queues that can be used to consolidate messages.
Mail Servers tab
When SqlAnswersMail Configuration starts for the first time, all
the fields on the mail tab are grayed out, except for the Add link. If this is not your first time
using the application and you have already added configuration settings for
mail servers in the past, you will see a list of configured servers in the Mail
Server drop-down.
To start configuring a new
mail server, click the Add link. You must then provide a
descriptive name of your mail server. This can be its DNS name, but it does not
have to be. (You can provide a DNS name in the Remote Server Name field later
in the configuration process.)
Other settings on the
Mail Servers tab include:
Sender's Email - Since SqlAnswersMail
uses SMTP to send email, the Sender's Email must be provided. The email address
you provide here will be used in the From field of
each email message sent by SqlAnswersMail.
Note In addition to
storing a sender's email in the configuration file, you can override its value
from the sp_sendSAM (see Usage
Guide for more information). This makes it possible to use different From addresses for different databases, processes, etc.
Default Domain – This parameter is provided for
compatibility with xp_sendmail. In MAPI/Exchange
world, it’s possible to send an email from xp_sendmail
to a mail recipient without fully-qualifying his/her email address. For
example, if an Exchange server is configured for a domain sqlanswers.com, one
can send an email from xp_sendmail to support and that email will go to support@sqlanswers.com.
When working with SMTP servers, this functionality is not available – all
email addresses must be fully qualified; therefore, SqlAnswersMail
provides the functionality to record a default domain for each SMTP mail
server. Once provided, this default domain will be appended to all email
addresses that do not have the domain portion. For example, you can specify
sqlanswers.com as the default domain, and then the following call to SqlAnswersMail will work: exec sp_sendSAM
@recipients=’support’, @subject=’test’. The email will
then be sent to support@sqlanswers.com.
Authentication - Select the type of
authentication you want to use. These are standard SMTP types: Anonymous,
Basic, and NTLM. If you need to provide a password, it will be encrypted and
stored in the SqlAnswersMail.ini file.
Timeout - Defines, in seconds, how long
to wait before giving up on establishing a connection. The default is 30
seconds.
Remote Mail
Server/Server Name
- Defines a location of the remote mail server. This location can be a DNS name
or an IP address unless you can reference your mail server directly by its BIOS
name from your SQL server.
Note In order to send
email using a remote mail server, you will need to configure the mail server to
accept connections from your sql server and to enable
mail relay for messages originated from your sql
machine.
Remote
Mail Server/Server Port - A network port to use when sending mail to a remote server. The default is 25.
Local Mail Server - If it is possible to reference
the pickup folder of your mail server directly from your SQL server, you can
drop emails straight to the pickup folder of your mail server. In IIS, the
default folder for mail pickup is C:\Inetpub\mailroot\Pickup.
Once you are done with
the setup of your mail server, click Save and then click Test to test your configuration. You will be asked for an email address
where you can send a test email. After you provide the email and click OK, you
will either get a confirmation that the email has been sent or an error
explaining why the email cannot be sent. In the latter case, you will need to
correct your configuration settings.
Note If you get a
confirmation that your email has been sent successfully, you still need to
check that the email has been actually delivered to the email address you
specified. This is necessary because in some situations, mail servers will
reject email without generating errors that can be programmatically trapped.
One common cause of email being rejected is when your mail server is not set up
to relay email for your sql server. You will need to
make adjustments on the mail server itself to resolve this problem.
SQL Servers tab
If no sql
servers have been previously configured, all controls on the Sql Servers tab will be grayed out, except for the Add link. To add a new server, click Add to open the New Sql Server Instance dialog.
From there you can setup the sp_Send SAM Procedure,
which is the entry point to the SqlAnswersMail
solution.
Note SqlAnswersMail can service more than one sql server instance as long as they are installed on the
same machine. Each instance has to be configured separately.
When the New Sql Server Instance
dialog starts, you can see a list of your local instances in the Server Name
drop-down. Select an instance and then choose how you want to connect to it:
using trusted authentication or sql native
authentication. We recommend that you use an account that has sysadmin privileges for this sql
server instance to avoid any setup problems related to insufficient
permissions. At the very minimum, the account you use to connect needs to have
enough rights to create stored procedures and execute the GRANT statement.
In the Database
drop-down, select a database in which you want to install the sp_sendSAM procedure. We recommend that you install sp_sendSAM in the Master database because it will then be
visible to all other databases on your sql servers.
You can click on the Review
Procedure link to view the
source code of the sp_sendSAM stored procedure. The
source code is provided, so DBAs can stay informed of
what exactly is being installed on their database servers.
Next, you need to set up
security settings for sp_sendSAM. There are two
options: All users of the SQL server or Sys Admins
only. When you choose the first option, the procedure will be set up to be
accessible to the PUBLIC role. When you choose sys admins
only, the security around this procedure is not configured. It will default to
no access unless you have accounts in your system with privileges to run any
stored procedures.
Allow Temporary Tables in Queries option is used to allow SQL Server
temporary tables to be used in the @query parameter or <sql></sql> blocks. Checking this option will give more
flexibility to your code since temp tables often provide a convenient way to
preprocess data. Consider this example:
create table #Email (email varchar(500))
insert #Email select EmailAddress from Customers
where DateCreated > GetDate()
– 30
exec sp_sendSAM
@recipients='<sql>select email from #Email</sql>',
@subject='Hello new member'
In order for this query to run successfully, it’s necessary to have the
Allow Temporary Tables in Queries option checked because #Email is a temporary
table. The disadvantage of having this option on is that inside sp_sendSAM a fragment of dynamic SQL is executed in order
to copy the data from a temporary table to a global temporary table
that’s visible to SqlAnswersMail. This presents
a security vulnerability since any code that executes dynamic SQL can be
exploited for SQL injections. In order to lower the risk of injections, sp_sendSAM disallows the comment symbol (two hyphens) in <sql></sql> blocks or the
@query parameter. It’s recommended that you check Allow Temporary Tables in Queries only when sp_sendSAM
is used by a controlled set of users, and it’s absolutely necessary to
use temporary tables.
Click Setup Stored
Procedure to install sp_sendSAM in the database
of your choosing.
Here are some additional
settings on the Sql Servers tab:
Appearance/Default Format - Choose Text or HTML as your
default format for sending email. The default is Text. This parameter can be
overridden from sp_sendSAM by using @format and @attachment_format parameters (see Usage
Guide for more information).
Appearance/Stylesheet - When you send email in the HTML format, an HTML stylesheet can be applied to your message and/or
attachment. By default, SqlAnswersMail provides a stylesheet called default.css.
You can define your own stylesheets and place them in
the same folder where SQLAnswersMail.ini file is located (by default C:\Program
Files\SQLAnswers\Mail). Once in that folder, the stylesheet names will be available in the Stylesheet drop-down. You can also override this parameter
from sp_sendSAM.
Appearance/Encode HTML
- This setting determines how the inline HTML is treated when sending
results of a query in the HTML format. If Encode HTML is checked, then all
inline HTML content will be encoded with entity references, and therefore the
markup will not be rendered as HTML. If this setting is unchecked, then all
inline HTML markup will be passed “as is” to the output buffer.
Note You can override
this setting from the stored procedure by using the ToHTML:
token. Please consult the Usage Guide for more information about this token.
Mail Servers/Primary
Server - This
setting allows you to choose the primary mail server to be used with this sql server instance. This setting must be provided.
Mail Servers/Backup
Server - You have
an option of providing settings for a backup mail server, which will be used to
send email when the primary mail server is not available.
Note When you use a
local pickup folder, the email will be placed in the folder even if the mail
server is offline; therefore, the Backup Server configuration is specifically
applicable to remote servers only.
Database Connection - One of the strong points of SqlAnswersMail is its ability to send results of sql queries as attachments. In order to do that,
SqlAnswersMail.exe needs to establish a call-back connection to the sql server to retrieve the data. The credentials you supply
here are used to establish this call-back connection and to retrieve the
results of a sql query you provided when calling sp_sendSAM.
Note The password is
encrypted and stored in the local INI file.
Timeout - This is a command timeout in
seconds. The default is 300 seconds (5 minutes).
Width - The width parameter is provided
for compatibility with xp_sendmail. The width of 0
means unlimited width. Xp_sendmail defaults to 80
characters.
Other Settings
Email When Query Empty – check this option in order to
instruct SqlAnswersMail to send an email even if the
@query parameter produced no results. If this option is off, an email will not
be generated if there are no results to send.
Force Set User –
use this option to further secure your SqlAnswersMail
installation. When this checkbox is set, then SqlAnswersMail
will automatically try to use the security context of a calling user.
Prohibited
Tokens – yet another security setting. You can
provide a list of tokens that are not allowed in the @query parameter or <sql></sql> blocks. You
may choose to restrict all DML statements and anything that’s prefixed
with xp_, which will prohibit the usage of extended
stored procedures such as xp_cmdshell in calls to sp_sendSAM.
Redirect all email to
– by providing an email address in this field you will redirect all
messages to a particular email address regardless of what’s been passed
in @recipients, @copy_recipients or @blind_copy_recipients parameters. This field is especially
useful on a development server where you may be working on a piece of a
production code that would otherwise send messages to end users.
Once you are done with configuration,
click Test to test functionality of the configured SQL Server
instance. If the email has been successfully delivered, then you are done with
configuring the core functionality of SqlAnswersMail.
If you want to take advantage of queuing, please read on to the next section;
otherwise, you can save and close SqlAnswersMail
Configuration and proceed to the Usage Guide
for instructions on how to use sp_sendSAM.
Queues tab
Queuing is useful when
you need to consolidate multiple emails into one message. Suppose you have a
number of data feeds that transfer data between systems and notify a business
manager of any data changes. Instead of sending a separate email from each
feed, you can queue all the emails until the end of the day and then send a
single consolidated email summarizing all the changes made during the day.
There are several ways
you can queue a message. On the Queue Tab, you can assign a daily or a weekly
schedule to a queue and then from SQL use the @queue parameter to queue email.
If adding a @queue parameter to your sp_sendSAM or xp_sendmail calls is not an option, you can setup a queue
with filters that will automatically queue all email that passes certain
conditions. To learn more about queuing please refer to the Usage Guide. The reminder of this section will
explain configuration options available on the Queues tab.
To add a new queue, click
the Add link. You need to provide a unique name for your new
queue.
Scheduler Settings
In order to queue a
message, SqlAnswersMail creates a task in the Windows
Scheduler, which requires a username and a password, so the task can execute at
times when nobody is logged on to a machine. Scheduler Settings section is not queue-specific – the same
username and password are used for all queues. Note that the username field
needs to include a domain such as ACME\BSmith. The
password is encrypted and stored in the SqlAnswersMail.ini file.
Default Subject Line -
when you queue multiple messages, you can use this setting to provide a
descriptive subject for a consolidated message. You can also override this
setting directly from a SQL call to sp_sendSAM.
Queue By Default – there are three options: None,
All, and Filter. When you select None (default) the
only way to queue a message is to provide the @queue parameter in a call to sp_sendSAM. When you choose All,
then all messages sent from a particular SQL Server instance will be queued.
When you choose Filter, then you can use the Filter’s Add/Edit/Delete
links to configure conditions, based on which messages will be either queued or
not.
The
Add link of the Filter section
brings up a dialog that let’s you use regular expressions to create
conditions based on the fields of a message. You will need to choose a field
such as Recipients or CallingSQL under the field drop
down and then provide an expression under the Regular Expression textbox. Here
are some examples of conditions that can be specified:
|
Field |
Regular Expression |
Explanation |
|
Recipients |
@acme.com |
Messages
to recipients whose email address has a domain @acme.com will be queued |
|
Subject |
Sales |
Any
email that has the word Sales in the subject will be queued |
|
Message |
^This
is a sales report |
Only
email that has a body that starts with This
is a sales report will be queued. |
|
CopyRecipients |
Only
email that has a single address, jdoe@acme.com,
in the Copy Recipients line will be queued |
|
|
CallingSQL |
spMarketingChanges |
Only
email that was generated from the body of a procedure spMarketingChanges will be
queued |
Queue Schedule –
use this section to configure a daily or weekly schedule of a queue.
sp_sendSAM should now be ready to use. Please proceed to the Usage Guide for explanations on how to use
this stored procedure.