SQL SERVER 2008 Configuration database mail– e-mail with SQL databases

Original: SQL SERVER 2008 Configuration database mail– e-mail with SQL databases

SQL SERVER Configuration database mail– e-mail with SQL databases

originally from:http://blog.sqlauthority.com/2008/08/23/ Sql-server-2008-configure-database-mail-send-email-from-sql-database/august, by Pinaldave

Today this article we discuss database Mail SQL server SQL server database Mail sql Server–difference between  database Mail and  SQLMail  database mail sqlmail SQL mail Database mail.  monica Thank you for making a wide range of tests and pictures for this article.

Use SQL Server requires three basic configuration steps to send e-mail messages

1) Create profile and Account 2) Configure email 3) to send e-mail .

Steps 1) Create profiles and Accounts

You need to create a profile and configure Database Mail Wizard to access the accounts that are used in the Database Mail node and its context menu in the Metabase Mail Management node. You can use this wizard to manage the global settings for accounts, configuration files, and Database Mail as follows:

Step 2) Configure mail :

after completing the account and profile creation, we need to configureDatabase Mail.to configure it, you first need to passsp_configureStored Procedures,enabledDatabase Mail XPsparameters, as follows:sp_CONFIGURE ‘show advanced‘, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs‘, 1
GO
RECONFIGURE
GO

Step 3) send mail :

Once all the configuration is complete, we can send the message , execute the sp_send_dbmailof the stored procedure ,and provide the required parameters as follows:

USE msdb
GO
EXEC sp_send_dbmail @profile_name=‘PinalProfile‘,
@recipients=‘[email protected]‘,
@subject=‘Test message‘,
@body=‘This is the body of the test message.
Congrates Database Mail Received By you Successfully.‘

after all validation of the input parameters is complete, the message is sort waits in Service Broker. For more information on this, see SQL server–introduction to Service Broker.

Database Mailwill keep a copy of the sent e-mail, which we canSysmail_allitems, sysmail_sentitems ,Sysmail_unsentitems,Sysmail_faileditemsto query. The sending status of the message is saved in theSysmail_mailitemstable , when sent successfully, the table Sent_statusThe column will be displayed as 1; When sending fails,thesent_status column will be displayed as 2, 3 whennot sent .
as shown below, the log file can beSysmail_logview in the table

Span style= "FONT-SIZE:9PT; Color:blue ">SELECT  *
FROM  sysmail_mailitems Span style= "FONT-SIZE:9PT; Color:black ">
GO
SELECT  *
FROM  sysmail_log
GO

The Send status can be Sysmail_sentitems validation in the table .

After you send it, you can check your email in your inbox, as shown in the message that the author received

SQL SERVER 2008 Configuration database mail– e-mail with SQL databases