.netsqlsql-serverconfigurationdatabase-mail

How to configure and run database mail in SQL Server


How to enable and run database mail in SQL Server 2008? I know that it need to

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 I want to see how can I configure this.


Solution

  • 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 = 'you@yourdomain.com',
    @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