Usage Guide
Once installation and configuration have been completed, you are ready to start using SqlAnswersMail.
The entry point for sending email is a stored procedure called sp_sendSAM which you installed during the configuration process of SqlAnswersMail (see Configuration Guide for more information). sp_sendSAM can be installed in any database, but we recommend that you install it in the Master database, since that makes it available to all other databases on your server.
sp_sendSAM implements 99% of features of xp_sendmail, a system extended stored procedure that comes pre-packaged with SQL server. The problem with xp_sendmail is that it relies on MAPI and, therefore, is unreliable for unattended execution.
There is a way you can rename sp_sendSAM to xp_sendmail, so that you can take advantage of additional features of SqlAnswersMail without re-writing any of your existing SQL code. For more information on this topic, please visit SqlAnswers support forums at http://www.sqlanswers.com/Forums.
Here is the interface of sp_sendSAM:
CREATE PROCEDURE [sp_sendSAM]
@recipients varchar(4000),
@message ntext = null,
@query varchar(7800) = null,
@attachments varchar(4000) = null,
@copy_recipients varchar(4000) = null,
@blind_copy_recipients varchar(4000) = null,
@subject varchar(4000) = 'SQL Server Message',
@type varchar(255) = null,
@attach_results varchar(5) = false,
@no_output varchar(5) = false,
@no_header varchar(5) = false,
@width int = 0,
@separator char(1) = null,
@echo_error varchar(5) = false,
@set_user sysname = null,
@dbuse sysname = null,
@timeout int = 300,
@sender_email varchar(200) = '',
@format varchar(10) = '',
@attachment_format varchar(10) = '',
@wrap_query varchar(5) = FALSE,
@css_stylesheet varchar(8000) = '',
@call_mode varchar(10) = 'mail',
@pdf_definition varchar(8000) = '',
@xls_definition varchar(8000) = '',
@doc_definition varchar(8000) = '',
@one_per_recipient varchar(5) = FALSE,
@zip_attachments varchar(5) = FALSE,
@zip_definition varchar(8000) = '',
@queue varchar(100) = '',
@queue_definition varchar(8000) ='',
@content_path varchar(1000) = '',
@content_path_definition varchar(8000)='',
@merge_from_query int = 0,
@merge_from_query_definition varchar(8000)='',
@ansi_attachment varchar(5) = '',
@importance int = 1,
@reply_to varchar(4000) = '',
@email_when_empty varchar(10) = 'DEFAULT',
@exception_mode varchar(10) = 'DEFAULT',
@quote_all varchar(5) = TRUE,
@mail_server varchar(100) = ''
The first block of parameters, starting with @recipients and ending with @dbuse, comes directly from the definition of xp_sendmail. The only parameter that's not implemented in sp_sendSAM is @type since it only applies to MAPI-based email. For a detailed description of the first block of parameters, see the help file for xp_sendmail.
Parameters @recipients, @copy_recipients, @blind_copy_recipients, @sender_email, and @subject can now be made data-driven by enclosing a query in a <sql></sql> tag. Here is an example:
exec sp_sendSAM
@recipients='<sql>select EmailAddress from northwind..emails</sql>',
@query='select * from northwind..orders',
@attach_results = 'true',
@attachment_format = 'pdf'
Note there is no table emails in the Northwind database – if you want to run this sample you will need to create a table called emails with one column, EmailAddress.
The @query parameter has been enhanced as well. In addition to containing one or more SQL statements, it’s now possible to include meta data that describes a resultset. This meta data should be contained within a comment block that immediately follows the SQL statement and can include TITLE, COMMENTS, and FOOTNOTE tags. Here is a sample that retrieves records from the Employees table and decorates the results with additional information:
exec sp_sendSAM
@recipients = 'info@domain.com',
@format = 'HTML',
@query = 'select top 2 * from northwind..employees/*<title>Query 1 title</title><comments>Query 1 comments</comments><footnote>Query 1 Footnote</footnote>*/'
You can find more information about the use of meta data under the @css_stylesheet section.
You can also embed query results in the content of the @message parameter by using placeholders that follow a pattern of {Query#} where # stands for an ordinal position of a SQL statement in the @query parameter. For example, if you have a query that contains two statements, you can issue the following command to embed these statements within the content of a message.
exec sp_sendSAM
@recipients = 'info@domain.com',
@message =
'<p>This message has two resultsets displayed side-by-side</p>
<table>
<tr>
<td valign=top>{Query1}</td>
<td valign=top>{Query2}</td>
</tr>
</table>',
@format = 'HTML',
@query = 'select FirstName, LastName, Photo from northwind..employees;
select ContactName, ContactTitle from northwind..customers'
If you want to embed query results in an attachment, you need to use the <attachment></attachment> tag. Here is an example:
exec sp_sendSAM
@recipients = 'info@domain.com',
@message =
'<p>The attachment has two resultsets displayed side-by-side</p>
<Attachment>
<table>
<tr>
<td valign=top>{Query1}</td>
<td valign=top>{Query2}</td>
</tr>
</table>
</Attachment>',
@format = 'HTML',
@Attachment_Format = 'HTML',
@Attach_Results = 'true',
@query = 'select FirstName, LastName from northwind..employees;
select ContactName, ContactTitle from northwind..customers'
The resulting attachment will have two resultsets embedded into an HTML table.
When generating query results in HTML, all the inline markup will be HTML encoded by default. For example, suppose you have a table called Regions that stores two values in the column RegionName:
<font color=red>West</font>
<font color=green>East</font>
If you use @query='select RegionName from Regions' to select information from the Regions table, all the inline HTML markup will be escaped with entity references, so the resulting email will show the data exactly as it’s shown above.
If you do not want to encode HTML markup you need to prefix the column with ToHTML: token. For example, the query statement above would need to be re-written to @query='select ''ToHTML:'' + RegionName from Regions'. The resulting email will then contain the word West colored in red and the word East colored in green.
@timeout - This parameter specifies how many seconds SQLAnswersMail.exe component will wait before generating the Timeout Expired error. Enter 0 for unlimited timeout. The default is 300 seconds.
@sender_email - Since we are using SMTP to send email, it's necessary to provide the Sender's Email that will appear in the From field of every email message sent from SqlAnswersMail. This value must be provided and be valid since some email servers will not send email for email addresses they don't recognize.
@format - The three supported formats are TEXT, PDF, and HTML. The default is TEXT. You can configure a stylesheet to be used for HTML emails in the SqlAnswersMail Configuration utility. This parameter can be set to PDF only when @call_mode is set to RESULTSET or STRING.
Use the @embed() structure to embed images into HTML. For example, the following call will send an email with ExampleImage.jpg in the background:
exec sp_sendSAM
@recipients='info@domain.com',
@format='html',
@message='<body style="background-image:url(@embed(C:\ExampleImage.JPG))"><p>test</p><p>test</p></body>'
@attachment_format - The three supported formats are TEXT, PDF, and HTML. The default is TEXT. You can configure a stylesheet to be used for HTML emails in the SqlAnswersMail Configuration utility. When this parameter is provided, @attach_results must be set to TRUE.
@wrap_query - Starting with SQL Server Service Pack 3, it's not possible to issue a query across linked servers in the security context set by SETUSER command. If you set @wrap_query parameter to true, then the query will be first executed inside the sp_sendSAM and the results will be stored in a temporary table. Then the results from the temporary table will be queried and emailed by SQLAnswersMail.exe.
@css_stylesheet - This parameter allows you to apply a stylesheet to a body or to an attachment of a message. There are two options for how this parameter can be used:
- Specify the actual content that would normally go inside the STYLE element. Example: .col1{width:20px} div.SAMContent TD.dt {padding-top:10px}. The stylesheet defined in the configuration file will still be attached to the email, but the settings in the @css_stylesheet parameter will override any settings in the predefined stylesheet.
- Specify the filename of a stylesheet located in the SqlAnswersMail folder. In this case, the stylesheet will be used instead of the one defined in the configuration file. If you choose to specify a filename, a stylesheet with this filename needs to exist in the folder where SqlAnswersMail is installed (default C:\Program Files\SqlAnswers\Mail\HTMLStylesheets).
Note The easiest way to create a stylesheet is to use Default.css as a template. This stylesheet is located in the SqlAnswers\Mail\HTMLStylesheets folder.
The following is an overview of the CSS classes that are used by SqlAnswersMail to generate messages in the HTML format. We start with a call to sp_SendSAM:
exec sp_sendSAM
@recipients = 'info@domain.com',
@subject = 'Test Email',
@query = 'select top 2 employeeID, firstname, lastname from employees/*<title>Query 1 title</title><comments>Query 1 comments</comments><footnote>Query 1 Footnote</footnote>*/',
@dbuse = 'Northwind',
@format = 'html',
@message = 'The body of the email goes here',
@css_stylesheet = 'div.SAMContent TD {height:30px} div.SAMContent table{width:600px}'
Note The values in red need to be adjusted to your environment.
The previous statement generates the following email.
This email is built by combining the CSS classes defined in the default.css and style information provided in the @css_stylesheet parameter.
Here is a tag reference for this email
As you can see, virtually every element in the HTML email generated by SqlAnswersMail can be customized using @css_stylesheet parameter.
@call_mode - The default is MAIL, which means that the results of a query will be sent by email. Other options are RESULTSET, STRING, CLEARQUEUE and RUNQUEUE. The last two parameters apply to queuing and will be discussed further, in the Queuing section.
Note PDF compression is only supported when @call_mode = ‘MAIL’
You can use @call_mode to instruct SqlAnswersMail to return the output of a query in PDF, HTML, or TEXT format directly to the calling program. RESULTSET will return the results as a one-column, multi-row resultset, and STRING will return the results as a one-column, one-row resultset. For example, if you execute the following piece of code from the Query Analyzer, you can then cut and paste results into a notepad and save the file as PDF.
exec sp_sendSAM
@query='select * from northwind..orders',
@call_mode = 'resultset',
@format = 'pdf'
Note You will need to configure the Query Analyzer to return results as a grid (CTRL-D) in order to avoid having a grid header in the resultset; otherwise, make sure not to include a header when doing cut-and-paste from Query Analyzer to Notepad.
Note When saving PDF results in Notepad, choose ANSI under Encoding.
Since the Query Analyzer has a limitation of 8,000 characters per returned row of data, you will need to use @call_mode = ‘RESULTSET’ when returning results back to the Query Analyzer directly. If you call sp_SendSAM from
Dim oRS As New Recordset
Dim sSQL As String
Dim oStream As TextStream
Dim oFS As New FileSystemObject
sSQL = "sp_sendSAM @query='select * from northwind..orders', @call_mode = 'string', @format = 'pdf'"
oRS.Open sSQL, "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=YOURSQLSERVER"
Set oStream = oFS.CreateTextFile(App.Path & "\Output.pdf", True, False)
oStream.Write oRS(0)
oStream.Close
Note For brevity, the example above does not use
@pdf_definition - This parameter allows you to format PDF results of your query. The default definition file is located in C:\Program Files\SqlAnswers\Mail\PDFDefinitions\defaultPDF.xml. You can make modifications to this file or create a copy and reference the copy in the @pdf_definition parameter. Assuming you made a copy of defaultPDF.xml called OfficialPDF.xml and placed this copy in the PDFDefinitions folder, you could make the following call to sp_sendSAM:
exec sp_sendSAM
@query='select * from northwind..orders',
@call_mode = 'resultset',
@format = 'pdf',
@pdf_definition = 'OfficialPDF.xml'
Each attribute in the PDF definition file can be overridden in the @pdf_definition parameter as well. For example, to change the sizetocontent parameter “on the fly”, we would use the following call:
exec sp_sendSAM
@query='select * from northwind..orders',
@call_mode = 'resultset',
@format = 'pdf',
@pdf_definition = 'OfficialPDF.xml;//doc/@sizetocontent=1'
Each overridden attribute needs to be referenced as //element/@attribute=value. The value should not be enclosed in quotes even when the value is a string. You can include multiple attribute references separated by a semicolon. The following example sets the orientation attribute to landscape and disables compression:
exec sp_sendSAM
@query='select * from northwind..orders',
@call_mode = 'resultset',
@format = 'pdf',
@pdf_definition = '//doc/@orientation=Landscape;//doc/@compress=0'
Here is a list of all the attributes available for use in @pdf_definition parameter:
|
Element/Attribute |
Definition |
@pdf_definition usage |
|
Document |
||
|
doc/margins |
Defines margins, in pixels: TOP, BOTTOM, LEFT, RIGHT. |
//doc/@margins=72,72,36,36 |
|
doc/orientation |
Defines the layout of the paper as Landscape or Portrait |
//doc/@orientation=Landscape |
|
doc/papersize |
Can be one of the prepefined paper sizes: |
//doc/@papersize=Legal |
|
doc/paperwidth |
Custom paper width, in pixels. Overrides papersize. |
//doc/@paperwidth=200 |
|
doc/paperlength |
Custom paper length, in pixels. Overrides papersize. |
//doc/@paperlength=200 |
|
doc/compress |
When set to 1, uses Flate compression. |
//doc/@compress=0 |
|
doc/sizetocontent |
When set to 1, expands paper width to fit all the content without wrapping long lines. |
//doc/@sizetocontent=1 |
|
Query Output |
||
|
title/isbold |
1 – bold, 0 – not bold. |
//title/@isbold=1 |
|
title/fontsize |
Font size of a title, in pixels |
//title/@fontsize=14 |
|
title/bottommargin |
A bottom margin that is printed after a title |
//title/@bottommargin=10 |
|
comments/isbold |
1 – bold, 0 – not bold. |
//comments/@isbold=1 |
|
comments/fontsize |
Font size of a comments, in pixels |
//comments/@fontsize=14 |
|
comments/bottommargin |
A bottom margin that is printed after comments |
//comments/@bottommargin=10 |
|
grid/isbold |
1 – bold, 0 – not bold. |
//grid/@isbold=1 |
|
grid/isheaderbold |
1 – bold, 0 – not bold. This attribute applies to the header part of the grid. |
//grid/@isheaderbold=1 |
|
grid/fontsize |
Font size of a grid, in pixels |
//grid/@fontsize=14 |
|
grid/bottommargin |
A bottom margin that is printed after a grid |
//grid/@bottommargin=10 |
|
footnote/isbold |
1 – bold, 0 – not bold. |
//footnote/@isbold=1 |
|
footnote/fontsize |
Font size of a footnote, in pixels |
//footnote/@fontsize=14 |
|
footnote/bottommargin |
A bottom margin that is printed after a footnote |
//footnote/@bottommargin=10 |
|
Page Headers |
||
|
header/isbold |
1 – bold, 0 – not bold. Defines whether information placed in the header of each page is bold. |
//header/@isbold=0 |
|
header/fontsize |
Font size used for page headers, in pixels |
//header/@fontsize=6 |
|
header/datetime/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a date/time in page headers |
//header/datetime/@display=left |
|
header/datetime/format |
Defines the format of a date. The rules for this attribute are the same as for the FORMAT function in VB 6. Here is a summary of some popular codes: |
//header/datetime/@format=MM/DD/YYYY hh:mm |
|
header/pagenumbers/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a pagenumbers in page headers |
//header/pagenumbers/@display=left |
|
header/pagenumbers/format |
Defines the format of page numbers. There are two placeholders that can be used in this context: %PAGE% - will be replaced with the actual page number %TOTAL% - will be replaced with the total number of pages |
//header/pagenumbers/@format=Page %PAGE% of %TOTAL% |
|
header/customtext/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a custom text in page headers |
//header/customtext/@display=center |
|
header/customtext/text |
Any text that will be placed in page headers |
//header/customtext/@text=ACME Corporation, Confidential |
|
Page Footers |
||
|
footer/isbold |
1 – bold, 0 – not bold. Defines whether information placed in the footer of each page is bold. |
//footer/@isbold=0 |
|
footer/fontsize |
Font size used for page footers, in pixels |
//footer/@fontsize=6 |
|
footer/datetime/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a date/time in page footers |
//footer/datetime/@display=left |
|
footer/datetime/format |
Defines the format of a date. The rules for this attribute are the same as for the FORMAT function in VB 6. Here is a summary of codes: |
//footer/datetime/@format=MM/DD/YYYY hh:mm |
|
footer/pagenumbers/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a pagenumbers in page footers |
//footer/pagenumbers/@display=left |
|
footer/pagenumbers/format |
Defines the format of page numbers. There are two placeholders that can be used in this context: %PAGE% - will be replaced with the actual page number %TOTAL% - will be replaced with the total number of pages |
//footer/pagenumbers/@format=Page %PAGE% of %TOTAL% |
|
footer/customtext/display |
Possible values are Left, Center, Right, and None. Use this attribute to set the visibility of a custom text in page footers |
//footer/customtext/@display=left |
|
footer/customtext/text |
Any text that will be placed in page footers |
//footer/customtext/@text=ACME Corporation, Confidential |
@xls_definition – When @attachment_format is set to XLS, you can further customize the appearance of the attachment with @xls_definition parameter. For example, the following SQL generates an Excel spreadsheet that has the header colored in red.
exec sp_sendSAM
@recipients = 'info@domain.com',
@query='select CustomerID, OrderDate, Freight from northwind..orders/*<title>This Title Is Red</title>*/',
@attach_results='true',
@attachment_format='xls',
@xls_definition='//format/@titlecolor=\red255\green0\blue0'
For a description of all available formatting settings, please refer to DefaultXLS.xml, located in the XLSDefinitions folder under the location where SqlAnswersMail was installed (default C:\Program Files\SqlAnswers\Mail).
@doc_definition – When @attachment_format is set to DOC, you can further customize the appearance of the attachment with @doc_definition parameter. For example, the following SQL generates an RTF document that has the header set to 24 pt Tahoma.
exec sp_sendSAM
@recipients = 'info@domain.com',
@query='select CustomerID, OrderDate, Freight from northwind..orders/*<title>This Title Is Red</title>*/',
@attach_results='true',
@attachment_format='doc',
@doc_definition='//format/@titlefontsize=24;//format/@titlefont=Tahoma'
For a description of all available formatting settings, please refer to DefaultDOC.xml, located in the DOCDefinitions folder under the location where SqlAnswersMail was installed (default C:\Program Files\SqlAnswers\Mail).
@one_per_recipient - When set to TRUE, each recipient listed in the @recipient parameter is emailed individually. When set to FALSE, one email is sent to all the recipients.
@zip_attachments – can be either TRUE or FALSE with a default of FALSE. When this parameter is set to TRUE, attachments are compressed into a zip file according to rules specified in the @zip_definition parameter.
Several options are available for file compression. You can specify whether you want all attachments compressed into a single file or different files. You can also specify an extension to be used for zip files in order to avoid your messages being blocked by email servers. You can also password protect your zip files, which provides a mechanism to further secure your email communications.
Compression configuration options are stored in XML files in the ZIPDefinitions folder under the folder where SqlAnswersMail is installed. In the ZipDefinitions folder, there is a file called defaultZIP.xml, which can be used as a template when creating customized zip definitions files. If @zip_definition parameter is not provided, the default parameters from defaultZip.xml will be used.
In order to provide customized compression settings, you need to pass @zip_definition parameter to sp_sendSAM. You can either specify a file name of a customized zip definition file, or you can provide customized settings directly. The following table provides an overview of what compression settings are available for customization.
|
Element/Attribute |
Definition |
@zip_definition usage |
|
|
||
|
File/extension |
Defines an extension to be used for a zip file. Use this option to avoid your emails being blocked by email servers. |
//file/@extension=zi_ |
|
File/password |
Use this option to password-protect your attachments |
//file/@password=MyPass |
|
File/name |
When Name is blank, each attachment will be compressed into a separate file. When this parameter has a value, all the attachments will be compressed into a file with the name provided. |
//file/@name=salereports |
Here are examples of how to use compression with SqlAnswersMail.
This example attaches results of a query and zips the attachment using default settings.
exec sp_sendSAM
@recipients= 'info@domain.com',
@query='select top 10 * from northwind..customers',
@attach_results=true,
@zip_attachments=true
Before running the following example we’ve created a file called CorporateZipPolicy.xml and placed it in the ZipDefinition folder. Then, we specify the name of this file in the @zip_definition parameter.
exec sp_sendSAM
@recipients='info@domain.com',
@query='select top 10 * from northwind..customers',
@attach_results=true,
@zip_attachments=true,
@zip_definition='CorporateZipPolicy.xml'
The following example specifies that the resulting zip should have an extension zi_.
exec sp_sendSAM
@recipients='info@domain.com',
@query='select top 10 * from northwind..customers',
@attach_results=true,
@zip_attachments=true,
@zip_definition='//file/@extension=zi_'
The next example demonstrates a message with two attachments, test.txt and the results of the query execution. Both of these attachments are compressed into a single zip file called TestZip.zi_ according to the @name parameter provided.
exec sp_sendSAM
@recipients='info@domain.com',
@query='select top 10 * from northwind..customers',
@attachments='c:\temp\test.txt',
@attach_results=true,
@zip_attachments=true,
@zip_definition='//file/@name=TestZip;//file/@extension=zi_'
The following example extends the previous one with a parameter @password that instructs SqlAnswersMail to password-protect zip files attached.
exec sp_sendSAM
@recipients='info@domain.com',
@query='select top 10 * from northwind..customers',
@attachments='c:\temp\test.txt',
@attach_results=true,
@zip_attachments=true,
@zip_definition='//file/@password=MyPass;//file/@name=TestZip;//file/@extension=zi_'
Queuing is a powerful feature when you need to combine a number of smaller messages into one larger email that’s then sent to an end user. Suppose you have a number of data feeds that transfer data between systems and notify a business manager of 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 one consolidated email summarizing all the changes made during that day.
There are four different ways to queue a message with SqlAnswersMail.
1. You can specify the date and/or time to send a message at in the @queue parameter. For example, the following call will queue the message until 5:00 PM of the same day.
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='5:00 PM'
2. You can provide the description of a queue other than date/time and then issue a command to process the queue. For example, we can queue a couple of messages into a queue called SalesReport:
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='SalesReport'
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='SalesReport'
Then, you can issue a command to process the queue as follows:
exec sp_sendSAM
@call_mode='RUNQUEUE',
@queue='SalesReport'
This is useful if you have a large procedure or a SQL script that’s supposed to send a number of notifications to an end user. Throughout the procedure you can queue messages and then at the end of the procedure you can send a consolidated message containing all the notifications.
3. You can also queue according to a predefined schedule. The Configuration Guide explains how to configure predefined schedules in the SqlAnswersMail Configuration utility. Once a schedule is configured, you can reference it using the @queue parameter.
Suppose we configured a schedule called DailyNotifications that’s scheduled to run every three hours. We can issue the following SQL statement in order to add an email to this queue.
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='DailyNotifications'
SqlAnswersMail will recognize the fact that the @queue parameter contains the name of a predefined schedule and queue the email until the earliest time DailyNotifications is scheduled to run.
4. If you already have a large number of calls to xp_sendmail or sp_sendSAM and you do not want to modify all these calls, you can use SqlAnswersMail Configuration Utility to setup default filters that will “trap” incoming email and queue automatically. These filters are described in details in the Configuration Guide.
If you have an existing queue that you would like to clear, you will need to call sp_sendSAM with @call_mode = 'CLEARQUEUE' and the name of the queue provided in the @queue parameter. For example, the following call clears a queue called MyTestQueue:
exec sp_sendSAM
@call_mode='clearqueue',
@queue='MyTestQueue'
@queue_definition parameter let’s you customize default settings stored in defaultQUEUE.xml (found in the QUEUEDefinitions folder) or any other queue definition files that you might have created using SqlAnswersMail Configuration Utility. The two most useful parameters in @queue_definition are title and body that let you specify the subject and body to be used in a consolidated email.
For example, the following call will queue two emails until 5:00 PM into one message that will have a subject line of “Sales Report”, and the body of the email will start with a text “This is a sales report for the Western Region”.
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='5:00 PM'
exec sp_sendSAM
@recipients='info@domain.com',
@subject='Test Queue',
@queue='5:00 PM',
@queue_definition='//queue/@title=Sales Report;//queue/@body=This is a sales report for the Western Region'
Content Path
You can point SqlAnswersMail to a web page located on the Internet or an intranet and instruct the application to load the content of a referenced page into the body of an outgoing email.
Suppose you need to send out a newsletter in the HTML format. You could create this newsletter in your favorite HTML editor and then post the page to a web server that’s visible to your recipients. You can then issue the following SQL statement to send the newsletter to your customers:
exec sp_sendSAM
@recipients='info@domain.com',
@content_path='http://www.sqlanswers.com/newsletters/SqlAnswersNewsletter092005.html'
where @content_path contains a URL of where your newsletter is located. SqlAnswersMail will load the content from the address provided, check all the SRC and HREF attributes to make sure they can be resolved by recipients and embed the content in the body of an email.
You can also point @content_path attribute to a location on your intranet by prefixing the path with file://. Suppose an HTML file you want to send is located at C:\Temp\NewsLetter.html. In order to forward this file to recipients you could issue the following statement:
exec sp_sendSAM
@recipients='info@domain.com',
@content_path='file://C:\Temp\NewsLetter.html'
SqlAnswersMail allows you to customize how SRC and HREF attributes are resolved. There are three options
- You can embed resources within a message
- You can have SqlAnswersMail resolve all SRC and HREF attributes to absolute paths.
- You can send HTML as is.
To control how SqlAnswersMail processes HTML, you use @content_path_definition parameter, which provides for customization of settings stored in defaultContentPath.xml (folder ContentPathDefinition) or any other custom path file you might have authored. In addition you can specify a username and a password to be used to open a URL that is password protected.
Here is a summary of different options that @content_path_definition supports
|
Element/Attribute |
Definition |
@zip_definition usage |
|
|
||
|
Tags/a |
Specifies whether to change href attributes of A tags to absolute URLs, embed referenced pages into an email or do nothing |
//tags/@a=absolute (default)
|
|
Tags/img |
Specifies whether to change src attributes of IMG tags to absolute URLs, embed images into an email or do nothing |
//tags/@img=absolute (default) |
|
Tags/link |
Specifies whether to change src attributes of LINK tags to absolute URLs, embed stylesheets into an email or do nothing |
//tags/@link=absolute (default) |
|
Tags/Others |
Specifies how to handle tags other than A, IMG, or LINK. |
//tags/@others=absolute (default) |
|
Tags/Contentfolder |
SqlAnswersMail will try to automatically identify the folder where a page specified in the @content_path attribute is located. Sometimes SqlAnswersMail will get confused by periods in folder names and you will then need to help SqlAnswersMail identify the folder using the contentfolder parameter. |
Suppose the file we want to send is located at |
|
Tags/Contentbaseurl |
SqlAnswersMail will try to automatically identify the base URL from a full URL provided in the @content_path attribute. Sometimes SqlAnswersMail will get confused by periods in folder names and you will then need to help SqlAnswersMail identify the URL using the contentbaseurl parameter. |
Suppose the file we want to send is located at |
|
Path/username |
Use this parameter to access URLs that are password-protected |
//path/@username=jdoe |
|
Path/password |
Use this parameter to access URLs that are password-protected |
//path/@password=mypass |
|
content/attach |
Use this parameter to create attachment from the HTML |
//content/@attach=1 |
Here is an example that accesses the newsletter mentioned above. SqlAnswersMail is instructed to embed all the images, resolve all the A links to absolute URLs, and use a username and password to load HTML.
exec sp_sendSAM
@recipients='info@domain.com',
@content_path='http://www.sqlanswers.com/newsletters/SqlAnswersNewsletter092005.html',
@content_path_definition='//tags/@img=embed;//tags/@a=absolute;//path/@username=jdoe;//path/@password=mypass’
With SqlAnswersMail you can issue a query and then embed the results of this query into different parts of the resulting email.
In order to do a mail merge you need to provide at least one query in the @query parameter; then you need to specify which query to use for mail merge in the @merge_from_query parameter. Here is an example:
exec sp_sendSA
@recipients='info@domain.com',
@subject='{Title}',
@message='Hello {FirstName} {LastName}',
@query='select top 3 * from northwind..employees',
@merge_from_query=1
During run-time, tokens {Title}, {Firstname}, and {LastName} are replaced with values from the corresponding fields. For each row in the resultset, a separate email is generated.
If, for some reasons, it’s unacceptable to use {} as field delimiters, you can specify other delimiters in the @merge_from_query_definition parameter. For example, the following call uses square brackets as delimiters:
exec sp_sendSAM
@recipients='info@domain.com',
@subject='[Title]',
@message='Hello [FirstName] [LastName]',
@query='select top 3 * from northwind..employees',
@merge_from_query=1,
@merge_from_query_definition='//merge/@startdelimiter=[;//merge/@enddelimiter=]'
Note that Mail Merge does not work with queuing, and you cannot merge content in attachments.
@ansi_attachment – when set to TRUE, SqlAnswersMail generates TEXT and HTML attachments in ANSI format. When set to FALSE (the default), attachments are generated in UNICODE.
@importance – the default is 1, which results in a message with normal importance. Setting this field to 0 will send out a message with low importance and setting it to 2 will produce a message with high importance.
@reply_to – you can optionally specify an email address that will be used as a Reply To email.
ollowing are some examples of sp_sendSAM.
@email_when_empty – this parameter allows you to control whether an email with empty query is sent to a user. Possible values are DEFAULT, TRUE and FALSE. By default, an email is sent out.
@exception_mode – this parameter controls the behavior when SqlAnswersMail contacts a mail server with a request to send an email. Possible values are STOP (default) and CONTINUE. If this parameter has a value of STOP, then SqlAnswersMail will abort the execution upon the first exception. If this parameter is set to CONTINUE, SqlAnswersMail will report errors to the calling program but will not abort the entire call. In the case of queuing, the report is logged in the event log as a warning. This parameter is useful is you use @one_per_recipient or @queue options in such a way that you do not want to abort the entire mailing campaign if only one email fails.
@quote_all - this parameter only applies to CSV attachments. When set to TRUE, double-quotes are placed around each field in the output, regardless of whether a field actually needs to be in double-quotes. When set to FALSE, double-quotes are placed around only those fields that need to be surrounded by double-quotes per the CSV standard. The default is TRUE.
@mail_server - you may choose to send email via a mail server other than the one configured for a particular SQL Server. Prior to using this parameter, it's necessary to set up a Mail Server profile using the SqlAnswersMail Configuration utility. The name of the profile can then be passed in @mail_server parameter.
Note The values in red need to be adjusted to your environment.
To send a simple text message…
exec sp_sendSAM
@subject = 'Test',
@message = 'Test Email',
@recipients = 'info@domain.com'
To send a message with an attachment…
exec sp_sendSAM
@subject = 'Test',
@message = 'Test Email',
@recipients = 'info@domain.com',
@attachments = 'C:\test.txt'
To send a message with an attachment that is generated from a query…
exec sp_sendSAM
@subject = 'Test',
@message = 'Test Email',
@recipients = 'info@domain.com',
@query='select top 10 * from northwind.dbo.customers',
@attach_results='true'
To send a message with a query results included in the body of the message in the HTML format…
exec sp_sendSAM
@subject = 'Test',
@message = 'Test Email',
@recipients = 'info@domain.com',
@query='select top 10 * from northwind.dbo.customers',
@body_format = 'html'
To send a message with a query results included as an attachment in the PDF format…
exec sp_sendSAM
@subject = 'Test',
@message = 'Test Email',
@recipients = 'info@domain.com',
@query='select top 10 * from northwind.dbo.customers',
@attachment_format = 'pdf',
@attach_results = 'true'