Generate an email notification when a table hits a geven amount of rows

Question

Generate an email notification when a table hits a geven amount of rows

James Millar-305032

Ten Centuries

Points: 1047
More actions
October 3, 2008 at 9:27 am

#213698

Hi,
Can anyone out there help me?
I need to find a way to create an email notification when a table in my db contains a certain amount of rows in a given period - i.e. if the table is populated with 1000 rows in an hour then an email notification is generated.
Is this even possible?
Many thanks in advance!
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Assuming that you have a datetime column on the table that is default based on the insert date / time then you could write a fairly simple query to do this as follows;

declare @reccount int

select @reccount = select count(*) from table1 where insertdt > DATEADD(MINUTE, DATEDIFF(MINUTE, '01:00:00', CURRENT_TIMESTAMP), '19000101')

if @reccount > 1000 then

xp_sendmail... You can fill in the rest.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Thanks for that David sounds like it will do the trick!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

If you're going to schedule it - perhaps just let the job scheduler send the e-mail. Xp_sendMail can get really funky, since it won't release until the e-mail is sent. If something goes funky during the sending process you can end up with processes that are stuck and just will not respond to a kill, won't cancel, etc.... Nothing short of a cold boot of the server seemed to do it.

Anyway - I avoid xp_sendmail like the plague if I can avoid it.

To have the job send you the e-mail - use the same logic David was getting at, with a little tweak:

declare @reccount int

select @reccount = select count(*) from table1 where insertdt > DATEADD(MINUTE, DATEDIFF(MINUTE, '01:00:00', CURRENT_TIMESTAMP), '19000101')

if @reccount < 1000

Select 1/0 --will cause the step to fail if the e-mail should not be sent.

And then - simply set the job to e-mail you upon success of the job.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 4

Thanks Matt that sounds even better as we are looking to add this to a job!

Thanks again

- James