Step 1Log in to SQL Server 2008 with a correct user name and password.
Step 2Expand the Management tab then select SQL Server Logs then right-click on Database Mail then select Configure Database Mail.
Step 3
The following window appears:
Click Next.
Step 4
Select
the
Set up Database Mail by performing the following tasks: radio button.
Step 5Click Next then a confirmation box appears; click OK on that.
Step 6When you click OK then write the profile name description and click on
Add.
Step 7Then a new window apears where you provide your profile name and configure
Outgoing Mail Server(SMTP) then click
Basic authentication and provide your email id and password then click OK.
Step 8Then in the new window it will show your profile, check that and click Next.
Step 9Then in the new window the
Configure System Parameters description is shown.
Step 10Click Next. It will show a confirmation about your profile; click Finish.
Step 11Now the email service has been configured, click Close.
Step 11Send a test email by right-clicking on database mail and click Send Test E-Mail.
Step 12Provide the Test Email Id and click On
Send Test E-Mail. Then check your mail to see if you got the mail. Enjoy!!
Note:
After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter using the
sp_configure Stored Procedure, as shown here:
- sp_CONFIGURE 'show advanced', 1
- GO
- RECONFIGURE
- GO
- sp_CONFIGURE 'Database Mail XPs', 1
- GO
- RECONFIGURE
- GO
Output Message
The Configuration option "show advanced options" changed from 0 to 1. Run the RECONFIGURE statement to install.
The configuration option "Database Mail XPs" changed from 1 to 1. Run the RECONFIGURE statement to install.
Use the following query to send mail using query:
Send Mail Query
USE msdb;--Must Use msdb databse otherwise you will get error.
- GO
- EXEC sp_send_dbmail @profile_name='MyTestMail',
- @recipients='[email protected]',
- @subject='My Test Mail Service.',
- @body='Database Mail Received Successfully.'
This is the body of the this message
Note:
If you are using a database other than msdb then use a stroed procedure like msdb.dbo.sp_send_dbmail. The log can be checked in sysmail_log table as shown below:
- SELECT * FROM sysmail_mailitems
- GO
- SELECT * FROM sysmail_log
- GO
- select * from sysmail_log
Summary
In this illustration you learned about configuring and sending mail using SQL Server 2008. Please provide your valuable comments about this article.