SQL Server Email Alerts Notifications

Then send the email to the desired recipients. In order to do this  we need to create a Database Mail Profile. More details on How to create a Database Mail Profile can be found in the Books Online.

exec msdb..sp_send_dbmail @profile_name = ‘Profile Name’
,@recipients =’Email Address’
,@subject = @mail_subject
,@body = @mail_body
,@body_format = ‘HTML’
,@importance = ‘High’
set @j = @j+1
end

drop table #alert_message
drop table #replicationalerts
drop table #subscriber_alert_message

That’s it, now you will have a much more user friendly email alerts notification system.

Download the full listing of Step 2 here

Pages: 1 2




Related Articles :

4 Responses to “SQL Server Email Alerts Notifications”

  1. Hi Satnam singh,

    grt work Awesome article
    I followed your article but i have one question . how can stored procedure usp_send_replication_alerts can send email automatically whenever replication fails ?

    Right now i can send email when i execute your stored procedure manually like

    EXEC [msdb].[dbo].[usp_send_replication_alerts]

    Any help is appreciated

    Thanks
    Niranjan

  2. I would like run this in my environment any step show me error, please help that I need this!!

  3. Hello Satnam Singh…
    I have also the same question as Niranjan.
    How can stored procedure(usp_send_replication_alerts) will send email automatically whenever replication fails??

    right now I have created trigger on sysreplicationalerts and it worked fine for sending mail automatically for define jobs in sql server agent. BUt when I deleted all jobs it didn’t work.

    create trigger send_mail
    on sysreplicationalerts
    after insert
    as
    begin
    EXEC [msdb]..[sp_send_dbmail]
    @profile_name = ‘testProfile’,
    @recipients = ‘it.ravi832@gmail.com; ravishankar_405@yahoo.com‘,
    @subject = ‘Error Message’,
    @body = ‘HI ALL !!! HOW ARE YOU’,
    @body_format = ‘HTML’
    end

    And I couldn’t fetch error message.

    I have same requirements like your company but I am unable to implement it.

    Can you explain your steps.

    Thanks in advance..

  4. Yes Satnam this is very useful, but if any of you have found out how to make this automated to receive an failure message directly when on failure it would be more useful. Should we have any scheduled jobs to kick that msdb exec sp step?
    Eben

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 |