sql alert on conditional data in table

Question

Post reply

sql alert on conditional data in table

arementer

SSC Enthusiast

Points: 104
More actions
April 13, 2010 at 8:46 am

#217862

I have a table that is updated with the current datetime when an outside module runs. it looks something like this:
id modulename processstamp
1 module1 4/13/2010 10:16:00 AM
2 module2 4/13/2010 10:26:00 AM
The table is updated every 15-30 minutes or so if the process was successful. The times should always be current if everything is peachy.
I am looking to use the sql server agent to send an alert if the "processstamp" datetime is older than 60 mins. That way I will be notified if there is a problem with the original processes.
Can anyone give me a suggestion on the best way to do this? links to samples would be great.
Thank you for any help you can give.

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Create a job that runs every 60 minutes that checks the date/time and send your response from there.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

I'm not a big writter of SQL code but the link above looks like it might give you a good starting point. Since you will need to calculate 60 minutes from the last processstamp captured in the table. The link above he is calculating durations between start and stop times so it is not an exact answer, but could point you in the right direction.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 3

With this table

==

d modulename processstamp

1 module1 4/13/2010 10:16:00 AM

2 module2 4/13/2010 10:26:00 AM

==

run a job like this

declare @newest datetime

select @newest = max(processstamp) from myTable <-- your table name here

if datediff(mi,getdate(),@newest) > 60 <-- use minutes mi instead of hh for hours

begin

xp_sendmail 'Send a message'

end

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

Something like this :

DECLARE @ProcessCount int

SELECT @ProcessCount = COUNT(id)

FROM MyTable

WHERE ProcessStamp < DATEADD(MM,-60,GETDATE())

IF @ProcessCount <> 0

BEGIN

EXEC msdb..sp_send_dbmail

@recipients = 'Bozo.D.Clown@MyCompany.com',

@subject = 'Process Update Status',

@body = 'Process Failed to update within the specified threshhold.'

END

Your recipient list can be SMS addresses as well.

Converting oxygen into carbon dioxide, since 1955.

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 5

Steve Cullen has good code. I'd wrap that in a stored procedure, make the minutes a parameter to the proc, and then schedule that in SQL Agent as a job.

arementer SSC Enthusiast Points: 104 More actions · Answer 6

Thank you all for your quick replies. Let me try this out and I will reply with my success. 🙂