Thursday, April 10, 2008

Using Database Mail in SQL Server 2005

SQL Server 2005 database mail is an easy to use feature that can be called by any application or code to send notification emails. This can be used by DBAs to monitor jobs and batches where there are hundreds of database servers and instances which have large number of periodic scheduled jobs.
SQL Server Database Mail is used to send emails from the SQL Server engine to a set of recipients. We can use the sp_send_dbmail stored procedure in the msdb database to send an SMTP notification emails.

Using the sp_send_dbmail Stored Procedure
The stored procedure msdb.dbo.sp_send_dbmail can be called from the application whenever a notification email has to be send. The only prerequisite required is that the SQL Server instance through which the database email is being sent should have an email profile created. This profile contains the required information such as sender email address, reply address, SMTP server name, SMTP port number etc. The section – Configuring the Email Profile – explains how to configure an email profile.
Following is the syntax for the sp_send_dbmail –
Execute msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients = ‘’,
@subject = ‘’,
@body = ‘’,
@importance = ‘’,
@body_format = ''

@profile_name – This should be the profile name to be used
@recipients – This should be the list of email addresses delimited by semi colon.
@subject – This should be the subject of the email.
@body – This should be the email body
@importance – This should be either ‘low’, ‘normal’ or ‘high’
@body_format – This should be either ‘TEXT’ or ‘HTML’

Example –
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail Profile',
@recipients = ‘abc@myComp.com; xyz@myComp.com’,
@subject = ‘Backup Completed’,
@body = ‘AppData database backed up successfully!’,
@importance = ‘high’,
@body_format = 'TEXT'
You can dynamically assign values to predefined variables and send the variable values to the parameters as well.
Configuring the Email Profile
Follow the below given procedure to configure SQL Server 2005 database mail.
§ Creating the Database Mail Profile
· Connect the SQL Server instance for which Database Mail has to be configured using SQL Server Management Studio.
· Open the “Database Mail Configuration Wizard” from “Management” à “Database Mail” à “Configure Database Mail”.
· If you are using Database Mail for the first time, select “Setup Database Mail” and click the “Next” button to proceed.
· In the “Ne Profile” window, do the following.
o In the “Profile Name” textbox, give the appropriate profile name. E.g. “TCO Database Profile”
o In the description textbox, give purpose of the profile you are creating.
o Under the “SMTP Accounts” section, click the “Add” button to add a new account. This will open up the window “Add a new Account to TCO Database Profile”. Click the “New Account” button.
1. In the “Account Name” textbox, enter the appropriate account name. E.g. “TCO Notification”
2. Give the purpose of the account in the “Description” textbox.
3. In the “Email Address” textbox, chose an appropriate email address. The database email will have sender as this email address. This address need NOT be a valid email address. E.g. TcoNotification@myComp.com.
4. In the “Display Name” section, enter any name that should be displayed as the sender name. E.g. “TCO Notification”
5. In the “Reply Email” section, enter any valid email address if you require that a reply email address is required.
6. In the “Server Name” section, enter the accurate name of the SMTP server through which the emails can be sent. E.g. “EmailServer-1.myComp.com”
7. In the “SMTP Authentication” section, select the appropriate authentication. By default, chose the “Anonymous Authentication”.
8. Click the “OK” button to save the changes. This will bring you back to “Database Mail Configuration” wizard.
NOTE: Contact your Network Administrator for the details on SMTP server details and the authentication information.
o Once the account has been created and configured, click the “Next” button to proceed.
o In the “Profile Security” section, select whether you want the profile to be a “Private Profile” or a “Public Profile”. A private profile can be use by only a single user, while a public profile can be used by any user. Click the appropriate security, and click the “Next” button to proceed.
o In the “Configure System Parameters” section, ensure that the parameter values are appropriate, else make any required change. Click the “Next” button to proceed.
o In the “Complete the Wizard” section, go through the various changes you have made. Click the “Finish” button to complete the configuration.
o Click the “Close” button to exit the wizard.

· You should test the profile you created to ensure the correctness. Follow the steps below to send a test mail.
o Open the “Send Test Mail” window from “Management” à “Database Mail” à “Send Test Mail”. o Select the profile created and enter your valid email address.
o Click the “Send Test Mail” button to send the test mail.
o Click the “OK” button to exit the window.
o Verify that you received the test mail. If you did not receive the test mail, review the configuration and try again.

2 comments:

UKdata House said...

We are committed to ensuring that all of the data from our data service is completelyup todate and accurate. This will make sure that you are only contacting consumers and businesses that have opted in to being contacted and are not on the telephone or mailings reference service. as per this blog
there have all information is best for business services.

Thanks & Regards
B2B Data

Jason Villegaz said...

Email servers and a 1300 number are important communication tools.