August 30, 2016 at 3: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 3:35 am
wendy elizabeth (8/30/2016)
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.
I think you've missed the point of a trigger here. A trigger event occurs when data is changed in some way in a table (you can choose which change it happens on, for example, INSERT, or UPDATE). You don't tell a trigger to activate, it happens immediately. Personally, I would suggest against this, as it seems your unfamiliar with them, and triggers can be very bad if done incorrectly. As you don't want to also change the Application database, then triggers aren't viable for your solution, but a couple of informative details below.
Firstly, if a trigger fails, for what ever reason (a conversion error, permissions, anything), the data isn't going to be updated or inserted in to the table the trigger occured on. This means that that data could effectively be lost.
If you're therefore setting up what is effectively an email trigger, the user adding to the table would need access to use sp_send_dbmail, which may not be advisable (and the user may actually be a user account which is just for that application). It would also need access to any other tables that contain details such as who to email, who their manager is, etc, which you may not even store in your application's database (I don't know your data so it might well be, but I'm just pointing it out). If that's the case, the application user(s) would also need access to other databases on your server where the email trigger can get the information.
From what you've said, you're happy for this to be a "reactive" response, that happens at the EoD, rather that proactive and tries to stop the issue at source. I would therefore suggest creating an Agent Job that runs an sp daily, that checks your data over. it can then email any "offenders", and will be happy to remind them the following day if they didn't resolve the issue.
wendy elizabeth (8/30/2016)
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?
This is quite a broad question, and impossible to really do, without writing your entire sp for you and knowing exactly what your data looks like. As i said above, what you're looking for the the sp_send_dbmail command, which you can read about on MSDB.
You'll need to build your parameters first, to addresses, from, email header, email body, etc. An very brief sql code for some data might look like this:
Create table #User (uID int identity(1,1),
Fullname varchar(50) NOT NULL,
email varchar(150) NOT NULL,
Position varchar(50),
mID int);
Insert into #User (Fullname, email, Position)
Values ('Joe Bloggs', 'jb@comp.com', 'CEO'),
('Steve Jones', 'sj@comp.com', 'Director'),
('Hannah Smith', 'hs@comp.com', 'Sales Manager'),
('George Smith', 'gs@comp.com', 'Sales Advisor'),
('Katie Price', 'kp@comp.com', 'Sales Advisor'),
('Liam Neeson', 'ln@comp.com', 'Sales Advisor'),
('Joanne Free', 'jf@comp.com', 'Receptionist');
Update #User
Set mID = 1
where Position = 'Director';
Update #User
Set mID = 2
where Position = 'Sales Manager';
Update #User
Set mID = 3
where Position = 'Sales Advisor';
Update #User
Set mID = 2
where Position = 'Receptionist';
Declare @emailBody nvarchar(max);
Declare @UserName varchar(50), @Email varchar(250), @ManagerEmail varchar(250)
Select @Email = u.email,
@ManagerEmail = m.email,
@emailBody = N'Dear ' + u.Fullname + ',
You have made an error on one of your files you did yesterday. Please ensure you address this as soon as possible.
Please note your manager, ' + m.Fullname + ' has also been notified.'
from #User u
left join #User m on u.mID = m.uID
where u.uID = 5;
Select @emailBody, @Email, @ManagerEmail;
Drop Table #User;
You can see the body is built using the query details, which you can then pass to sp_send_dbmail as a parameter.
Hope that sets you in the right direction.
Edit: Grammar corrections.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply