Tuesday, August 2, 2011

SQL Server 2008 Database Mail, step by step.

it's simple steps for working with database mail .
1- Enable Database mail feature.
2- Configure database mail .
3- Sending E-mail.

Step One :  Enable Database mail feature.

the following code will enable database mail feature .
sp_configure 'show advanced options', 1;
sp_configure 'Database Mail XPs', 1;
the result is
Step Two : Configure database mail .
1- Right click on database mail icon in the management folder, this folder found on server .
2- Select Configure Database Mail option on context menu. the welcome wizard will appear, click Next .

3- In the Select Configuration Task step, select Set up Database Mail by performing the following tasks :
this step will let to create Profile, account . and then click Next
check mail, you find your message there.

4- In the New Profile step, write on the following fields :
Profile name : MyProfile
Description : My Desc...

after that, press the Add button to add SMTP accounts  that will be associated with previous  profile .
the New Database Mail Account window will appear.

write on the following fields.
Account name : MyAccount.
Description : My Account Desc.
Email Address : Type your mail here.  
Display Name : your name.
Server name : SMTP site for your mail server. for example (smtp.gmail.com for GMAIL mail server)
Port number : enter the port number belongs to Server name (G-mail: 587) .

check on the checkbox of the This sever requires a secure connection (SSL) option.
after that in the SMTP Authentication section, select basic authentication and enter User name, Password.
note:  the factor of success sending mail is the right configuration that entered in this step .
Press OK   button to return again in the New Profile step . Press again to Add button if needed to create new account  for the same profile.

5- Press Next , the Manage Profile Security  step will appear.
in the first tab Public Profiles, select the profile from list to be a public profile . Or select the second tab Private Profiles.
in the Private Profiles, select the specific user with selected profiles that can be accessed by this user. and then Press Next.
6- Configure system parameter step, specify some system parameter if needed and then press Next .

7- The screen confirmation will appear, check your data again and then click Finish.

If all steps of Configuring form working fine, then click Close.

Step Three : Sending E-mail.
for send E-Mail type the following code :
USE msdb
EXEC sp_send_dbmail @profile_name='MyProfile',
@recipients='xxx@xxx.com', -- email that will be receive DB mail.
@subject='Test message subject !',
@body='Body of the test message.
Database Mail work successfully.'
and the result is,

Follow by Email