General discussion

Locked

Trouble creating a SQL SCRIPT

By rheal.dugas ·
I am in desperate need of some help creating a way to send out a email if one of my dispatches is not picked up.

I have created a database that dispatches calls to onsite analyst. When a request is entered it automaticly sends a email out to the analyst for that district. They reply to the request via a link in the email and that updates my DB filing a field (ACEPTEDDATE) that they have accepted it.
What I need is a trigger or job that will send that request out again if it is not picked up after one hour and also to send a email to their manager if it is not picked up within 2 hours.
The fields that I have are DATECREATED, ACEPTEDDATE, SENT
I would like to look at the time in DATECREATED and compare it to the current time and if it is one hour or more old and ACCEPTEDDATE is null put a number "1" into the SENT field. Once this happens it will send out a email to the same person. When the DATEDCREATED is 2 hours old, do the same thing but put a "2" in the SENT field.I have the script working for sending out emails, just need a way to trigger it.
Thank You

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Nerdie In reply to Trouble creating a SQL SC ...

OK, so the update statements to use would be something like this:

update table t
set t.sent = 1
where sysdate > (t.datecreated + 1/24)

update table t
set t.sent = 2
where sysdate > (t.datecreated + 2/24)

I supposed that the date columns are of type DATE and column SENT is of type NUMBER.

It's just that I wouldn't know a way to trigger your script, as it doesn't depend on any change in the table. A trigger won't work because of that. It should react on the current time, so you need some kind of web listener idea. Maybe there's something useful like that built into TOAD (Quest Software)...!

Success!

Collapse -

by brianbrinley In reply to Trouble creating a SQL SC ...

what your going to want to do is create a job in SQL Server that will either run a stored procedure or a specific query that will go into the database - select * from table where datecolumn < DatePart(hh,now())

then loop through these and send an email. You can schedule this to run however often you would like.

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Collapse -

by rheal.dugas In reply to Trouble creating a SQL SC ...

This question was closed by the author

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Forums