Introduction:
Database Mail is a new feature that comes with SQL Server 2005. It was very difficult to configure and send mails with the SQL mail feature in SQL Server 2000. In fact SQL mail had lot of prerequisites and had few problems known and unknown. With SQL server 2005 Database mail had got a great way of sending mails from SQL server without those issues.
What you need for configuring Database Mail?
All you need is the name of the Mail server and the port in which it sends mail. Mostly port 25 is used and hence its populated by default in the configuration.
Steps to configure Database Mail:
1. To configure Database Mail, navigate to the management folder under the SQL Server instance. Right and select the configure Database Mail option.
2. You will be taken to the welcome screen. Click next.
3. You will have three options here for creating a new account, modifying already existing account and changing system parameters. Click the first option to create a database mail profile.
4. In this screen you should give a name to your profile and a description that suits your profile name. And click Add.
5. You have to configure you mail parameters here. All you need to give is the mail address of the sender, associated name, mail server name or IP address and the SMTP port. You can also choose your authentication type that matches your need.
6. Here you need to say if this profile is a public or private. If public all users who have access to database mail can use this profile else it will be available only to you. Also you can configure the profile to be the default profile for sending mails.
7. These are the system parameters that you can configure yourself. Leave default if you do not need to change any parameters. Most would like to change the Max File Size parameter to accommodate their size.
8. This takes you to the complete page and click on finish.
9. You can find the status of the configuration. If it fails you can click on the task to see the error message and rectify the same.
10. Once this is completed try sending a test mail from the management folder, database mail. And check the database mail log to see if the mail has been sent successfully. If you get an error a detailed error message is displayed with which you can take corrective actions.
Now having seen how to configure database mail, let us look into sending mails using TSQL with the database mail profile that we have created.
The syntax for the database mail procedure and the parameters are given below.
| sp_send_dbmail [ [ @profile_name = ] ‘profile_name’ ] [ , [ @recipients = ] ‘recipients [ ; …n ]’ ] [ , [ @copy_recipients = ] ‘copy_recipient [ ; …n ]’ ] [ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; …n ]’ ] [ , [ @subject = ] ‘subject’ ] [ , [ @body = ] ‘body’ ] [ , [ @body_format = ] ‘body_format’ ] [ , [ @importance = ] ‘importance’ ] [ , [ @sensitivity = ] ‘sensitivity’ ] [ , [ @file_attachments = ] ‘attachment [ ; …n ]’ ] [ , [ @query = ] ‘query’ ] [ , [ @execute_query_database = ] ‘execute_query_database’ ] [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ] [ , [ @query_attachment_filename = ] query_attachment_filename ] [ , [ @query_result_header = ] query_result_header ] [ , [ @query_result_width = ] query_result_width ] [ , [ @query_result_separator = ] ‘query_result_separator’ ] [ , [ @exclude_query_output = ] exclude_query_output ] [ , [ @append_query_error = ] append_query_error ] [ , [ @query_no_truncate = ] query_no_truncate ] [ , [ @query_result_no_padding = ] query_result_no_padding ] [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ] |
A sample script which can be used to test if the mail profile works fine.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sugeshkr', @recipients = 'sugeshkr@gmail.com', @body = 'This message is from SQL Server.', @subject = ' This message is from SQL Server ' ;
You can also write queries in the procedures whose results will be send to the mail recipients as given below.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sugeshkr', @recipients = 'sugeshkr@gmail.com', @copy_recipients = 'kvs1983@gmail.com', @query = 'select @@servername' , @subject = 'servername', @attach_query_result_as_file = 1 ;
Leave a Reply