Sql server sending messages

Other SQL Server Topics
1

In a t-sql 2012 database, I need to run a script that checks to see if there is more than 1 record in a student table for the current school year. Once I find there is an issue, I want to send an email message to the user that caused the problerm and their supervisor.

I need to run this type of a script since users update the student table with vendor software. I have no control over when the user updates the table incorrectly.

Due to the facts I just listed above, I have the following questions to ask you:

1.Where would you place this script and why? I am thinking the script could be placed in a trigger, as part of a nightly cycle that updates the tables associated with the 'overall student process', or a job that is executed on weekdays by a sql agent. **Note: I do not want to setup a trigger since I do not want to change any part of the vendor application that updates the student table.

2.Would you show me how to setup the email message and execute the sql to place the data in the appropriate areas of the email, and how to get the email messages to be sent out?

2

  1. You need to contact the vendor to see if they'll allow a trigger on the table. Often times, a modification like that would violate the contract. If you do use a trigger, do not send the message in it. Instead, write a row to a table and then have an Agent job check that table and send an email if it finds a new row.

  2. You can use Database Mail for this. Plenty of example online. Search for sp_send_dbmail.

1 Like