Database mail.

http://articles.techrepublic.com.com/5100-10878_11-6161839.html


Database Mail, a new addition to the SQL Server 2005 database engine, is as simple to use as it is useful. Destined to be the replacement for SQL Mail, Database Mail uses a Simple Mail Transfer Protocol (SMTP) server to send e-mails rather than using the MAPI accounts that SQL Mail required. This allows your organization to send e-mails with attachments, e-mail query results, attach query results, and format HTML e-mails. It also gives you the ability to set many other configuration settings without requiring you to have an Exchange Server or configuring any type of MAPI workaround.

The advantages of Database Mail Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!
Besides being totally SMTP based, Database Mail has many other advantages:

It runs outside of the database engine, so the stress placed on the database engine is minimal.
It is cluster aware and fully supported in a clustered environment.
Its profiles allow for the redundant use of SMTP servers. (I go into more detail about this later in the article.)
It allows you to send a query text as a parameter to the stored procedure, which will execute the query and send the results in the e-mail.
The messages are sent asynchronously via a Service Broker queue, so you will not have to wait for a response when sending an e-mail.
It has multiple security features for sending e-mail, such as a filter for what attachment extensions can be sent and a governor for attachment size.
Setting up and using Database Mail
A bit of planning is required before you can set up a Database Mail solution. First, you need to have an available SMTP server so that e-mails can be sent. If you do not have an available SMTP server, read Microsoft Knowledge Base article 308161 for tips on setting one up. If you are not sure whether your organization has an SMTP server, talk to your network administrator to obtain the machine name or the IP address of the server. Your network administrator may need to configure the server so that e-mails can be sent from your SQL Server machine.

In Database Mail, the Account holds information that the database engine uses to send e-mail messages. An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

To send a Database Mail e-mail, a Profile must be used. A Profile is set up of one or more Accounts. This Profile-Account setup is very useful for a couple of reasons. It allows you to associate more than one Account to a profile, which means that you can associate more than one e-mail server to a profile. So, when you try to send an e-mail, each Account for the profile is tried until the message is successfully sent, which is great in case one or more of your SMTP servers is unavailable. It also allows you to develop your application code for sending e-mails without worrying about changing the Profile name for different environments. You can use the same Profile name for your Development and Production environments; the only difference is that the Accounts contained in the profiles will be different.

It is time to take a look at how to set up a Database Mail account. For our example, I will assume that you are sitting in front of a development machine for which you have sysadmin access. If you are not, you will need to be a member of the DatabaseMailUserRole in the msdb database.

The following script sets up some variables that I will use throughout the example. Note that the entire script will be run in the context of the msdb database, where Database Mail objects are stored.

USE msdb

GO

DECLARE @ProfileName VARCHAR(255)

DECLARE @AccountName VARCHAR(255)

DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddressVARCHAR(128)

DECLARE @DisplayUser VARCHAR(128)
Here I am setting up our ProfileName, AccountName, STMP server name, and the name that will display in the From field in the e-mail.

SET @ProfileName = 'DBMailProfile';

SET @AccountName = 'DBMailAccount';

SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';

SET @DisplayUser = 'The Mail Man';
The script in Listing A does some clean up work, so if I run the script again, I won't have to worry about errors.

The following section adds our Account, Profile, and Profile-Account association to the system.

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @AccountName,

@email_address = @EmailAddress,
@display_name = @DisplayUser,

@mailserver_name = @SMTPAddress



EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @ProfileName



EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @ProfileName,

@account_name = @AccountName,

@sequence_number = 1 ;
Now that everything is set up, I will send a test e-mail.

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'chapman.tim@gmail.com',

@body= 'Test Email Body',

@subject = 'Test Email Subject',

@profile_name = @ProfileName
To see if the message was sent successfully, I can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems