Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Along with reporting, do you want the insert to continue or be interrupted with an error? –  Robert Miller Jul 20 '11 at 6:51
    
I want it to continue. –  Orca Jul 20 '11 at 6:59
add comment

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
1  
I agree with using the trigger. This also satisfies not having to create any stored procedures. –  StanleyJohns Jul 20 '11 at 10:25
    
Thanks, that's what I did. Do you have any idea how to automate the Database Mail setup procedure.? –  Orca Jul 21 '11 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 Jul 21 '11 at 23:01
    
Thanks a lot, Marian –  Orca Jul 24 '11 at 6:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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