6

I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server.

I want to use that to send email on the event that certain data is added, for example if I have a table with a column called 'error no' in it; I want to be notified via email when a certain error no. is being added.

e.g. if error no is 5, I want to be sent an email via the notification system. If the error no is 4, then insert it to table with no notification.

I would love to be able to do it without writing any stored procedure or T-SQL, but if I have to, then I have no problem doing it.

Thank you guys.

Improve this question
2
  • Along with reporting, do you want the insert to continue or be interrupted with an error?
    – Robert Miller
    Commented Jul 20, 2011 at 6:51
  • I want it to continue.
    – Orca
    Commented Jul 20, 2011 at 6:59

1 Answer 1

Reset to default
10

The quickest solution I can think of would be to create on your specific table an after insert trigger where, based on your conditions (the error no..etc), you'd call the system procedure msdb.dbo.sp_send_dbmail to send a custom email.

You'd have first to configure the mail (create a profile and test it). I don't really think there's a simpler way.

I think that you could create an alert based on a custom error number. But this means that you'll have to modify your current code that inserts data in that table to throw this custom error no, to be able to see the alert.

Improve this answer
3
  • 1
    I agree with using the trigger. This also satisfies not having to create any stored procedures.
    – StanleyJohns
    Commented Jul 20, 2011 at 10:25
  • Thanks, that's what I did. Do you have any idea how to automate the Database Mail setup procedure.?
    – Orca
    Commented Jul 21, 2011 at 10:47
  • 1
    Please follow the article SQL Server Database Mail and SQL Agent Mail setup by using a script and you'll see the steps for automating the db mail.
    – Marian
    Commented Jul 21, 2011 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.