In a sql server 2012 database, I need to run a query and check to see if there is more than 1 record per student for the current school year in a table called studentdata.
I cannot put a trigger on the column in the table that shows there is more than 1 record for the student in the current school year since the data is generated using vendor software.
The vendor will not support the applicaton if I change there software.
Thus to check for duplicates and email the appropirate schoool staff, I have the following questions:
- To send out the email messages, is it better to use outlook email or sql server email? Would you tell me one why one method is better than the other? In addition, would you show me the sql to use and/or point me to a refernce that will show me how to setup the email message?
- To have this sql executed, would it be better to place the sql in an existing sql job that is run every night between a Monday or Friday night? Otherwise would it be better to have this sql generated as a scheduled job? If this is a scheduled job, would yoyu set this up as an SSIS package? The only purpose would be to send out the email message and an applicable report? Would you show me what method is better and tell me why that method is better? In addtion would you show me the sql on how to setup this job and/or point me to urls (links) that will tell me how to accomplish this goal?