Help with Stored Procedure which runs every 30 minutes..

Question

Post reply

Help with Stored Procedure which runs every 30 minutes..

Chitown

Hall of Fame

Points: 3390
More actions
June 18, 2014 at 2:17 pm

#292510

I would like to create a stored procedure which runs every 30 minutes. The main purpose for this is to notify the manager when a table reaches certain amount of records. Let's say it sends out an email when the table reaches 1500 records or 1000 records where Name = "Jim". It doesn't have to run every time there is an update or insert or delete (DML). Just need some help with the syntax and what would be the best approach to accomplish this?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You start with a simple count.

DECLARE @CountRows INT;

SELECT @CountRows = COUNT(1)

FROM myTable

WHERE Name = 'Jim';

Then you compare the count against a certain condition. If true, send an email.

IF @CountRows > 10000

BEGIN

sp_send_dbmail ... (all the parameters I can't remember)

END

Wrap this in a stored procedure and schedule it every 30 minutes with SQL Server Agent.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chitown Hall of Fame Points: 3390 More actions · Answer 2

Thank you:-). I am assuming this is all I need other than setting up DB mail and SQL job to run this procedure.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Chitown (6/18/2014)
Thank you:-). I am assuming this is all I need other than setting up DB mail and SQL job to run this procedure.

I think that's about it yes 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chitown Hall of Fame Points: 3390 More actions · Answer 4

I think that's about it yes 🙂

What happens if there is no activity in the DB? The job will run regardless but will it send out email notification even if there is no activity for a day?

AND

What happens if the job runs at 6PM and there are multiple users accessing the data, pulling more than 2000 rows let's say at 6:05, 6:10 all the way to 6:45?

Will the system keep track of all the transaction and send out email at 7 notifying me and giving me the list of tables which were accessed between 6 and 7 and reached more than 500 rows?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Chitown (6/19/2014)
I think that's about it yes 🙂
What happens if there is no activity in the DB? The job will run regardless but will it send out email notification even if there is no activity for a day?
AND
What happens if the job runs at 6PM and there are multiple users accessing the data, pulling more than 2000 rows let's say at 6:05, 6:10 all the way to 6:45?
Will the system keep track of all the transaction and send out email at 7 notifying me and giving me the list of tables which were accessed between 6 and 7 and reached more than 500 rows?

In your original question you only mentioned row counts.

You never said anything about tracking activity and/or transactions.

What do you mean with "pulling rows"? Is it storing data into a table?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chitown Hall of Fame Points: 3390 More actions · Answer 6

In your original question you only mentioned row counts.
You never said anything about tracking activity and/or transactions.
What do you mean with "pulling rows"? Is it storing data into a table?

Sorry about the confusion. I was thinking of something else but please disregard my last reply. I just needed help with a stored procedure that can detect the number of records in a table. My manager wants to be notified when table reaches 500 records where name = "Jim". The script you have provided me with is all I need.

Thanks!