E-Mail Functionality in SQL Server 2005



Working With E-Mails From Database Mail

sp_send_dbmail is the built-in stored procedure in the msdb database that can be used to send e-mails. The following is a simple example of using sp_send_dbmail.

To send mail, you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = ‘dinesh’,
     @recipients = ‘anybody@anywhere.com’,
     @body = ‘Use new feature of Database mail. No MAPI client needed’
     @subject = ‘New Feature of Database Mail’;

Another important feature in this stored procedure is the ability to send query results. Let us try a real world example. Very recently, a client asked me to send a daily e-mail of the error log count. You can use @query and @attach_query_results_as_file parameters to satisfy above requirement:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = ‘Error Logs’,
     @recipients = ‘DBA@customer.com’,
     @query = ‘SELECT COUNT(*) FROM crm.Admin.errorlogs
          WHERE DATEDIFF(dd, “2006-09-02″, getdate()) =0′,
     @subject = ‘Error Log Count’,
     @attach_query_result_as_file = 1 ;

This feature is available in SQL Mail, too. However, in SQL Mail you cannot send it an attachment, which is possible in Database Mail. In addition, there are also a several new parameters in sp_send_dbmail.

@body_format specifies the format of the e-mail message: TEXT or HTML.

@importance specifies the importance of the e-mail message.

@sensitivity specifies the sensitivity of the e-mail message.

Let us look at where this information is stored in SQL Server 2005. System views in the msdb database contain the e-mail messages and attachments sent from Database Mail and the status of each message.

Database Mail updates these tables when each message is processed. Query the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, and sysmail_faileditems views to check the delivery status of an individual e-mail message.

The sysmail_mailattachments view contains the e-mail attachments in Database Mail messages.



Converting SQL Mail to Database Mail

There are several changes required for changing over to Database Mail.

As specified before, sp_send_dbmail was used to send mail while in SQL mail xp_sendmail was used. So you will need to map both stored procedure parameters

xp_sendmail Argument

sp_send_dbmail Argument

@recipients

@recipients

@message

@body

@query

@query

@attachments

@file_attachments

@copy_recipients

@copy_recipients

@blind_copy_recipients

@blind_copy_recipients

@subject

@subject

@type

N/A

@attach_results

@attach_query_result_as_file

@no_output

@exclude_query_output

@no_header

@query_result_header

@width

@query_result_width

@separator

@query_result_separator

@echo_error

N/A

@set_user

N/A

@dbuse

@execute_query_database

(Source: SQL Server 2005 Documentation)

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

One Response to “E-Mail Functionality in SQL Server 2005”

  1. Dinesh,

    Thanks you very much for this article.
    I was able to set up a email account and send some test mails.
    Few questions:

    1) The profile that i created is public bit i didn’t made it default since my server has many databases and i will be using the mail profile for only one database.
    So should i make it private or leave it public(not default) ?

    2) With every schedule maintenance activity
    like database refresh or database shutdown\restart, do i need to manually check for the profile that i created still exists or not ?

    Thanks,
    Sumit

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |