How to Configure and run DataBase Mail in SQL Server

Question

How to enable and run Database Mail in SQL Server 2008 . I know that it need

Enabling Service Broker

Configuring SMTP (a Mail server is needed)

Using Configuration Storeprocedure.

I don't know what's the relation between application and dataBase mail. Actually How to enable Database mail for a RollBack and Commit Transaction ? (not for all SP , just for some of them)

---

Update : Database Mail is a Service which automatically sends mail (or sms) to a person which you specify in the configuration. You can specify that this event (sending mail) where and when fired. So Iwant to see how Can I configure this.

Answer 1

I cannot think of any way to configure db mail to send for every rollback or transaction, that would be a little overkill, and it sounds like you want to be selective about it anyways.

What you can do though is add a call to the email sp after each commit / rollback in your stored procedures. Here's a quick example:

BEGIN TRAN
-- sql operations here
COMMIT

-- send email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB Alerts',
@recipients = '[email protected]',
@body = 'Commit completed for ...',
@subject = 'SQL Commit/Rollback event';

You can read more about the parameters for sp_send_dbmail here: http://msdn.microsoft.com/en-us/library/ms190307.aspx

Hope this helps