August 30, 2016 at 8:18 pm
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?
August 31, 2016 at 6:35 am
Isn't this a duplicate of http://www.sqlservercentral.com/Forums/Topic1813840-3077-1.aspx ? Try to keep it to one topic for a question, it makes it confusing. I have answered in your original topic.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 31, 2016 at 6:52 am
I've done something similar to this recently, and added it as part of a SQL Agent job to mail out the results:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'blah@blah.com',
@subject = 'Daily Alert report',
@body = 'Daily Alert Report attached',
-- To have the query results as an attachment in the email you receive
@attach_query_result_as_file = 1,
@query = N'
Add in your t-sql here to find the results you need
',
@query_attachment_filename = 'Daily Alert Report.csv'
Hope this helps!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply