programming4us
         
 
 
SQL Server

SQL Server 2008 : Sending and Receiving with Database Mail

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/4/2011 4:23:18 PM
If you’re building client applications that rely heavily on Database Mail, it’s crucial to gain an in-depth understanding of its underlying architecture. The following sections provide detailed information on its inner workings.

The Service Broker Architecture

SQL Server relies on Service Broker (SSB) to activate the Database Mail process (DatabaseMail.exe) used to send mail. DatabaseMail.exe uses ADO.NET to connect to SQL Server and to read from and write to SSB queues (found in msdb) that hold send requests and send statuses in the form of typed SSB messages. You can view these queues (InternalMailQueue and ExternalMailQueue) in the Object Browser by selecting Service Broker and then the Queues folder. If you look a bit further in the Object Browser, you see how the mail transmission architecture is implemented (in part) as an SSB application, as you find the corresponding internal and external Database Mail SSB services (InternalMailService and ExternalMailService), SSB message types (SendMail and SendMailStatus), and a single SSB contract (SendMail/v1.0).

SSB’s involvement with Database Mail works like this:

  1. sp_send_dbmail (as the SSB initiator) is invoked and returns immediately. Under the covers, this adds an SSB message of type SendMail to the SSB mail queue, activating the undocumented internal stored procedure sp_ExternalMailQueueListener. Note that the mail message itself is saved to one or more of the msdb tables (such as sysmail_unsentitems and sysmail_attachments) if there are any attachments.

  2. SSB launches DatabaseMail.exe (running under the credentials of the SQL Server service), which, in turn, connects back to SQL Server, using Windows Authentication.

  3. DatabaseMail.exe reads the queued SSB send message, retrieves the mail message data, sends the email, and, finally (acting as the SSB target), places a message of type SendMailStatus in the mail status queue, reporting on the mail sending success or failure.

  4. When there’s nothing left to be sent in the outbound queue, and the maximum process idle time has been reached, DatabaseMail.exe exits.

By using SSB, Database Mail inherits the reliability of the SSB message transmission architecture.

Sending Email

The SSB queues that Database Mail uses must first be enabled before you can send mail from a session. You do this by executing the msdb stored procedure sysmail_start_sp. This procedure is similar to its predecessor, xp_startmail (as it must be called before sending), except that it has no parameters and, of course, has nothing to do with MAPI. It returns 0 or 1, indicating success or failure. If you don’t call this procedure, you receive this error message:

Mail not queued. Database Mail is stopped. Use sysmail_start_sp to
start Database Mail.

To temporarily disable SSB’s activation of the mail process, you execute sysmail_stop_sp (also with no parameters), which returns 0 or 1. If you send mail from code after this disabling this process, these messages will be queued. The external process is not started until sysmail_start_sp is called again. To check on the status of Database Mail, you can execute sysmail_help_status_sp (with no parameters). To check on the status of the queues, you execute sysmail_help_queues_sp.

After you execute sysmail_start_sp, you’re ready to begin sending mail using the sp_send_dbmail stored procedure. It has 21 parameters, most of which are optional. As the query engine will tell you if you try to execute it with no or too few parameters, at least one of the following parameters must be specified: @body, @query, @file_attachments, or @subject. You also must specify one of the following: @recipients, @copy_recipients, or @blind_copy_recipients.

Note

For the following T-SQL examples to work, you must first configure a default profile using either the Database Mail Configuration Wizard or Database Mail stored procedures, as detailed earlier.


A minimally parameterized test call might look like the following:

exec msdb.dbo.sp_send_dbmail @body='Testing...', @subject='A Test',
@recipients='test@samspublishing.com'
go
Mail Queued.

Table 1 describes the parameters, their types, and the xp_sendmail parameters to which they may correspond, to help you along in converting your existing T-SQL code.

Table 1. Parameters for Database Mail Stored Procedure sp_send_dbmail
ParameterDescriptionxp_sendmail Parameter to Which It Corresponds
@profile_nameThe sysname of the profile whose SMTP accounts will be used to send.Not available in xp_sendmail.
@recipientsA varchar(max) semicolon-delimited list of the recipients’ email addresses.Same as xp_sendmail.
@copy_recipientsA varchar(max) semicolon-delimited list of the carbon copy recipients’ email addresses.Same as xp_sendmail.
@blind_copy_recipientsA varchar(max) semicolon-delimited list of the blind carbon copy recipients’ email addresses.Same as xp_sendmail.
@subjectThe nvarchar(255) email subject.Same as xp_sendmail.
@bodyThe nvarchar(max) email body.Was @message in xp_sendmail.
@body_formatOne of the two varchar (20)'HTML' or 'TEXT' (the default). email format type strings, either Not available in xp_sendmail.
@importanceOne of the three varchar (6)'Low', 'Normal' (the default), or 'High'. email importance strings, either Not available in xp_sendmail.
@sensitivityOne of the four varchar (12)'Normal' (the default), 'Personal', 'Private', or 'Confidential'. email sensitivity strings, either Not available in xp_sendmail.
@file_attachmentsAn nvarchar(max) semicolon-delimited list of absolute paths to files to attach.Was @attachments in xp_sendmail.
@queryAn nvarchar(max) T-SQL code string to be executed when the message is sent. The code is executed in a different session than the calling session, so variable scope is a consideration.Same as xp_sendmail.
@execute_query_databaseThe sysname of the database in which the T-SQL in query is to be executed.Was @dbuse in xp_sendmail.
@attach_query_result_as_fileA bit value indicating whether the results of the T-SQL in query should be an attachment (1) or appended to the body (0; the default).Was @attach_results in xp_sendmail.
@query_attachment_filenameThe nvarchar(255) filename for the attached query results (as per @query and @attach_query_result_as_file). If not specified, the generated filename is arbitrary (usually QueryResults [some number].txt)In xp_sendmail, the first filename in @attachments was used.
@query_result_headerA bit value indicating whether the query result (1; the default) should include the column headers.Was @no_header in xp_sendmail.
@query_result_widthAn int value (defaulting to 256; you specify a number between 10 and 32767) indicating how wide a line in the query results should be before line wrapping occurs.Was @width in xp_sendmail.
@query_result_separatorA char(1) value (defaulting to a space) that indicates the query results column separator.Was @separator in xp_sendmail.
@exclude_query_outputA bit value that indicates whether to suppress the query output (such as rowcounts, print statements, and so forth) from being printed on the query console. 0 (do not suppress) is the default.Was @no_output in xp_sendmail.
@append_query_errorA bit value that indicates whether to send the email if the query to be executed raises an error. If set to 1, the error message is appended to the query output, and the query window for the session also displays the error (“A severe error occurred on the current command. The results, if any, should be discarded.”). If set to 0 (the default), the message is not sent, and sp_send_dbmail1. returns Not available in xp_sendmail, but similar to @echo_error.
@query_no_truncateA bit value that indicates whether to truncate query results having long values (such as varchar(max), text, xml, and so on) greater than 256. It defaults to 0 (off). Microsoft warns that using this can slow things down, but it is the only way to properly send these types.Not available in xp_sendmail.
@mailitem_idAn output parameter, an intmailitem_id of the message.  value indicating the unique Not available in xp_sendmail.

 

Note that the @type and @set_user parameters for xp_sendmail are not available. @type, of course, is obsolete because it is MAPI specific. @set_user is also obsolete because the content of the T-SQL to be executed may contain an EXECUTE AS statement.

Now that you’re familiar with the flurry of mail sending options, let’s look at a few examples and then examine how to track your sent messages by using the system views. Both of the following examples rely on sending via the default profile of the current user context. If the user has a default private profile assigned, it is used. If not, the default public profile is used (as in these examples). If there is no default public profile, an error is raised.

The example shown in Listing 1 sends an email containing an xml result to a recipient as an attached Extensible Application Markup Language (XAML) document, retrieved from the AdventureWorks2008.Production.Illustration column.

Listing 1. Sending XML as an Attachment with Database Mail
USE AdventureWorks2008
GO
DECLARE
@subject nvarchar(255),
@body varchar(max),
@query nvarchar(max),
@IllustrationId int,
@query_attachment_filename nvarchar(255),
@mailitem_id int

SELECT
@IllustrationId = pi.IllustrationId,
@subject = 'XAML for "' + pm.Name + '" attached. '
FROM Production.Illustration pi
JOIN Production.ProductModelIllustration pmi
ON pmi.IllustrationId = pi.IllustrationId
JOIN Production.ProductModel pm
ON pm.ProductModelID = pmi.ProductModelID

SELECT
@body =
N'Attached, please find the XAML diagram for illustration #' +
CAST(@IllustrationId as nvarchar(10)) +
'. A XAML browser plug-in is required to view this file.'

SELECT @query =
N'SELECT Diagram FROM Production.Illustration
WHERE IllustrationId = ' + CAST(@IllustrationId as nvarchar(10))

SELECT @query_attachment_filename = N'PM_' +
CAST(@IllustrationId as nvarchar(10)) + '.xaml'
exec msdb.dbo.sp_send_dbmail
@subject=@subject,
@body=@body,
@recipients='test@samspublishing.com',
@query=@query,
@execute_query_database='AdventureWorks2008',
@attach_query_result_as_file=1,
@query_attachment_filename=@query_attachment_filename,
@query_no_truncate=1,
@exclude_query_output=1,
@query_result_width=32767,
@mailitem_id=@mailitem_id OUTPUT

SELECT sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = @mailitem_id
GO
sent_status sent_date
---------------------
unsent NULL
(1 row(s) affected)



Note that you must set @query_no_truncate to 1 and @query_result_width to the maximum (to be safe) value for the attached query results to contain consistently well-formed XML. In addition, you should not include any carriage returns or line feeds in the body of the message, or the SMTP servers may not be able to send it.

The example in Listing 2 sends some query results as a comma-separated value (CSV) file that can be imported into programs such as Microsoft Excel. (You need to use the Get External Data command to accomplish this with Excel 9.)

Listing 2. Sending CSV Data as an Attachment with Database Mail
USE AdventureWorks2008
GO
DECLARE @mailitem_id int, @tab char(1)
SET @tab = char(13)

exec msdb.dbo.sp_send_dbmail
@subject='D. Margheim, Contact Info',
@body='Attached is Diane Margheim''s contact info, in CSV format.',
@recipients='test@samspublishing.com',
@query=N'SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE BusinessEntityId = 8',
@execute_query_database='AdventureWorks2008',
@attach_query_result_as_file=1,
@query_attachment_filename='DMargheim.csv',
@exclude_query_output=1,
@query_result_separator=',',
@mailitem_id=@mailitem_id OUTPUT

SELECT sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = @mailitem_id
GO
sent_status sent_date
---------------------
unsent NULL
(1 row(s) affected)



Notice that in both of these code listings, the values selected from the sent_status and sent_date columns of sysmail_allitems indicate that the mail has not yet been sent. The reason is that mail sending (like all other SSB messaging) is asynchronous: The message is immediately queued, and the Mail process later picks it up and sends it.

Receiving Email

The only way for SQL Server 2008 to receive email is by using the legacy stored procedures, such as sp_processmail, with SQL Mail. Database Mail does not support receiving incoming messages because there is no IMAP or POP3 support. This may have something to do with the fact that receiving email can represent a major security risk. Imagine what a denial-of-service attack on a database cluster could do to an organization. Or consider the danger of an incoming email request resulting in the execution of a query such as DROP DATABASE X. Most SQL Server data is too precious to jeopardize in this manner. Microsoft has also made it clear that SQL Mail will be phased out in the next release of SQL Server. Plus, there are many better alternatives to using this methodology, such as using native Web services , using .NET CLR-integrated assembly code .

Other -----------------
- SQL Server 2008 : Setting Up Database Mail
- SQL Server 2008 : Security and Compliance - Setting Up Auditing via T-SQL & SQL Injection Is Easy to Do
- SQL Server 2008 : Security and Compliance - SQL Server Auditing
- SQL Server 2008 : Security and Compliance
- SQL Server 2008 : Transparent Data Encryption
- SQL Server 2008 : Data Encryption - Column-Level Encryption
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- SQL Server 2008 : Data Encryption
- SQL Server 2008 : Client Data Access Technologies
- SQL Server 2008 : Client Configuration
- SQL Server 2008 R2 : Client Installation
- SQL Server 2008 R2 : Client and Server Networking Considerations
- Upgrading to SQL Server 2008 : Upgrading Other SQL Server Components
- Upgrading to SQL Server 2008 : Slipstreaming Upgrades
- Upgrading to SQL Server 2008 : Upgrading Using a Configuration File
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 2) - Upgrading In-Place
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 1) - Side-by-Side Migration
- Upgrading to SQL Server 2008 : Using the SQL Server Upgrade Advisor (UA)
- SQL Server 2008 : Developing Custom Managed Database Objects (part 7) - Using Transactions & Using the Related System Catalogs
- SQL Server 2008 : Developing Custom Managed Database Objects (part 6) - Developing Managed Triggers
 
 
 
Top 10
 
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS