Send an e-mail when a new record is added
-
I have an Auditing database that audits almost everything that happens from another application. The purpose is so that the auditors can see who's been doing what. However they only want to know one particular event. I have created a view when the event id gets added to the main table. For example there are event id's from 1 through to 10, but the view will only show event id 9.
How do I then have a new event added to that table, it sends it off to the selected people via e-mail automatically? I just want it to send the updated records at the end of every day not every time one is added.
Hope this makes sense.
Thanks,Kris
-
which sql server u r using ? i'm not sure about sql2000, but for sql2005,
it is quite simple, as u can write trigger to your table and each time a new record is inserted use the dbmail to sent an email.
or if u would sent email at the end of each day, you can create a job to execute a storedprocedure(to sent email) and set its schedule to daily.
of course before u can use the dbmail function, make sure u hav set the profile correctly.
-
I'm using 2005. The storedprocedure was the way I was going for. Then I could (as you say) create a job to run everynight. I already have several e-mails that get sent from the server, so that's not a problem.
I specifically want to know how to right the storedprocedure.
Thanks,Kris
-
the storedprocedure should be as simple as passing paremeter into the system storedprocedure:
EXEC msdb.dbo.sp_send_dbmail
@recipients = @emailto,
@copy_recipients = @emailcc,
@blind_copy_recipients = @emailbcc,
@subject = @emailsubject,
@body = @emailbody,
@file_attachments = @emailattach,
@body_format = 'HTML'
and execute it! hope i did not misunderstand what u said.
-
O.K I've had a play around with this and my question is now. When I run the script, it says that the mail queued. According to BOL that means it's a success. But I have not received an e-mail. I've tested the e-mail and I get it normally. I've noticed in the activity logs there is a process running where the wait time is just growing and growing on one activity.
The other question how do I then have it only send new entries and don't send it at all if there are no changes?
This is what I have so far.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'kris'
@recipients = 'kris@work.com',
@query = 'SELECT * FROM db1.dbo.table1 INNER JOIN
db1.dbo.table2 ON
db1.dbo.table1.col1 = db1.dbo.table2.col1
WHERE (db1.dbo.table1.col2 = 9)' ,
@subject = 'email',
@attach_query_result_as_file = 1
Thanks for all your help so far
Thanks,Kris
-
Your not going to believe it. Obviously the Christmas break wasn't enough for me. I had a typo in the e-mail address
Still the question still remains is there away to send just the new entries since the last time the job was run?
Thanks for all your help
Thanks,Kris
-
If you want to send the email immediately Use the trigger
or
if your table datetime column then use that column check and send the new records using a procedure and schedule that procedure to run every 5/10/30 minutes or so....
MohammedU
Microsoft SQL Server MVP -
I've actually refined the script a little as below:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'krha',
@recipients = 'kris@work.com.au',
@query = 'SELECT * from db1.dbo.view1' ,
@subject = 'email',
@attach_query_result_as_file = 1
I was hoping someone could just help me with a query of something along the lines of, if date of a new is today run this script if not don't bother running.
Thanks,Kris
-
i agreed with addict, to sent email for only new entries, use trigger.
if u wan to check the record date before sending email, why don u just put a IF ... ELSE statement before calling the msdb.dbo.sp_send_dbmail in the storedprocedure?
So everynight the Job execute the storedprocedure will only sent email with records created on the same day?
-
That's what I want to do but I'm not sure of the syntax due to it being a timedate statement.
Thanks,Kris
-
I have a timestamp on a table I do somethign similar ( a report of DDL changes in the last 24 hours). The part of the where clause looks like this:
posttime >= dateadd(hh,-24,getdate())
I actualy have -25 to have some overlap jic.
Alternatively you could have a bit field that is set whenever you send the email to indicate it has been emailed, then just select those that have not been marked as sent.
-
Thanks for that. That worked perfectly.
I hate to be a pain. But I now need an IF clause. If there is no new data don't send an e-mail. Can you help me out with that syntax. I know I'm pushing the friendship. But I'm pretty sure once I get that done it should be perfect.
Thanks,Kris
-
if exists (select 1 from from tablename where ....)
begin
sendmai...
end
MohammedU
Microsoft SQL Server MVP -
I've put the following
IF EXISTS (SELECT * from db1.dbo.view1 WHERE
(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'krha',
@recipients = 'kris@work.com.au',
@query = 'SELECT * from db1.dbo.view1
(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))',
@subject = 'email',
@attach_query_result_as_file = 1
END
And when I parse it, it says it's successful but when I go to execute it I get Incorrect syntax near '>'. I get the same error when I remove the extra brackets.
What am I missing this time?
Thanks,Kris
-
You are mssing WHERE cluase in your send mail query..
@query = 'SELECT * from db1.dbo.view1 WHERE
(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))',
MohammedU
Microsoft SQL Server MVP
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply