Configure database email function on SQL Server

The following script demonstrates how to use a Gmail account in SQL Server 2005/2008 to configure the database email function.

 

-- ===================================================== ============

-- Enable database mail

-- ===================================================== ============

EXEC sys. sp_configure N 'show advanced options', 1

RECONFIGURE;

 

EXEC sys. sp_configure 'database Mail XPs ', 1;

RECONFIGURE;

 

 

-- ===================================================== ============

-- Configuration

-- ===================================================== ============

DECLARE

@ Account_name sysname,

@ Profile_name sysname;

SELECT

@ Account_name = N 'gmailtest @ gmail.com ', -- database email account

@ Profile_name = n'gmail '; -- database mail configuration file name

 

-- Database email account

EXEC msdb. dbo. sysmail_add_account_sp

@ Account_name = @ account_name,

@ Email_address = N 'gmailtest @ gmail.com ', -- Email address

@ Display_name = N 'gmail test', -- reply address

@ Mailserver_name = n'smtp .gmail.com ', -- Gmail smtp server address

@ Port = 25, -- port number (for Gmail, use 456 if 25 is not general)

@ Username = n'gmailtest @ gmail.com ', -- Gmail email address

@ Password = n'abc. 123 ', -- Gmail mail account password

@ Use_default_credentials = 0,

@ Enable_ssl = 1; -- enable SSL communication encryption

 

-- Database mail configuration file

EXEC msdb. dbo. sysmail_add_profile_sp

@ Profile_name = @ profile_name;

-- Add the database email account to the mail configuration file

EXEC msdb. dbo. sysmail_add_profileaccount_sp

@ Profile_name = @ profile_name,

@ Account_name = @ account_name,

@ Sequence_number = 1;

 

-- Grant any user the permission to use the database mail configuration file

EXEC msdb. dbo. sysmail_add_principalprofile_sp

@ Principal_name = N 'guest ',

@ Profile_name = @ profile_name,

@ Is_default = 0;

 

 

-- ===================================================== ============

-- Send email

-- ===================================================== ============

EXEC msdb. dbo. sp_send_dbmail

@ Profile_name = @ profile_name,

@ Recipients = n'gmailtest @ gmail.com ', -- recipient address

@ Subject = n' test mail from database', -- mail subject

@ Body = n' this is a test mail'; -- mail content

 

-- 6. view the mail sending status:

Use msdb

Go

Select * from sysmail_allitems

Select * from sysmail_mailitems

Select * from sysmail_event_log

 

-- ===================================================== ============

-- Delete Configuration

-- ===================================================== ============

/*--

DECLARE

@ Account_name sysname,

@ Profile_name sysname;

SELECT

@ Account_name = N 'gmailtest @ gmail.com ',

@ Profile_name = n'gmail ';

 

-- Delete the database email account from the mail configuration file

EXEC msdb. dbo. sysmail_delete_profileaccount_sp

@ Profile_name = @ profile_name,

@ Account_name = @ account_name;

-- Delete a database email account

EXEC msdb. dbo. sysmail_delete_account_sp

@ Account_name = @ account_name;

 

-- Delete the database email configuration file

EXEC msdb. dbo. sysmail_delete_profile_sp

@ Profile_name = @ profile_name;

 

 

-- Disable the database email Function

EXEC sys. sp_configure 'database Mail XPs ', 0;

RECONFIGURE;

 

EXEC sys. sp_configure N 'show advanced options', 0

RECONFIGURE;

--*/

 

'

-- ===================================================== ============

-- Send attachments

-- ===================================================== ============

 

The ability to send file attachments via email is very important for productivity. The following script will send an electronic mail with an attachment named fileattachment.txt in it and store it on my drive C.

Make sure that the file exists so that no errors will occur during the sending process.

EXEC msdb. dbo. sp_send_dbmail
@ Recipients = n'chapman. tim@gmail.com ',
@ Body = 'message body ',
@ Subject = 'message subobject ',
@ Profile_name = 'database-mailProfile ',
@ File_attachments = 'C: // FileAttachment.txt ';

-- ===================================================== ============

-- Sensitivity and importance

-- ===================================================== ============

Typically, you need to mark an email as sensitive or important so that the recipient knows the information and requires careful protection. The following script will send an email with the sensitivity as private and the importance as advanced. In addition, the script copies the mail address yourname@yourdomain.com to the letter.

EXEC msdb. dbo. sp_send_dbmail
@ Recipients = n'chapman. tim@gmail.com ',
@ Body = 'message body ',
@ Sensitivity = 'Personal ',
@ Importance = 'high ',
@ Copy_recipients = 'chapman. tim@gmail.com ',
@ Subject = 'message subobject ',
@ Profile_name = 'database-mailProfile ';

-- ===================================================== ============

-- Query Result

-- ===================================================== ============

An important feature of the Database Engine with email sending capabilities is that it allows you to send a query to the sending process. This feature is available in both SQL Mail and Database Mail. A new feature in Database Mail is that the query result can be used as an attachment, which is shown in the following script. If you do not use the query result as a file, it will be placed in the mail body. See list B.

EXEC msdb. dbo. sp_send_dbmail

@ Recipients = n'chapman. tim@gmail.com ',
@ Body = 'message body ',

@ Subject = 'message subobject ',
@ Profile_name = 'databasemailprofile ',
@ Query = 'select Product FROM sb2.. SalesHistory group by Product having count (*)> 3 ',

@ Attach_query_result_as_file = 1,
@ Query_attachment_filename should 'results.txt'

Letters in HTML Format

My favorite new feature in database mail is that you can format your mail in HTML format. In simple terms, this is no big deal, but it does bring about some good possibilities. In the previous example, the query result you sent is included in the body of the email. Because this example contains only one column of data, it seems that there is no inconvenience.

However, when the query result contains multiple fields, the formatting becomes worse and worse. The good news is that you can use some XML and HTML features in SQL Server 2005 to format your query result set. Therefore, you can easily view the query result in a letter. The script in list C demonstrates how to send an XML/HTML email using the query results.

DECLARE @ xml NVARCHAR (MAX) DECLARE @ body NVARCHAR (MAX)
SET @ xml = CAST (SELECT Product AS 'td ', '', SUM (SalePrice) AS 'td'
FROM SalesHistory group by Product for xml path ('tr'), ELEMENTS) as nvarchar (MAX ))
SET @ body = 'EXEC msdb. dbo. sp_send_dbmail
@ Recipients = n' chapman. tim@gmail.com ', @ body = @ body, @ body_format = 'html', @ subject = 'message subobject', @ profile_name = 'databasemailprofile' when you send an HTML-format email, you can embed the query result into the query part of the email to format the query result to make it easier to see. (Though, you may argue that the yellow background I use in the letter does not look very comfortable.

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/fallingstarNo1/archive/2009/11/22/4853061.aspx