General discussion

Locked

Trouble creating a SQL SCRIPT

By rheal.dugas ·
Trouble creating a SQL SCRIPT
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 joesherr In reply to Trouble creating a SQL SC ...

Create an application with a timer in it. VB.Net will work. At intervals selected by you in the program, run queries to determine if the time has elapsed. You can use The DATEDIFF function in the query to identify the intervals that have expired.

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Collapse -

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

I also posted thiss on another Board and received a answer. There reply was to create a job that would run a stored procedure at whatever interval that I wanted.
Below is the SP script, Does anyone have any Ideas for a better solution?

CREATE PROCEDURE [rheal].[onehourcheck] AS
UPDATE dbo.ONSA_NSS
SET sent = 1
WHERE sent IS NULL
AND (datecreated BETWEEN DATEADD(hh, - 2, GETDATE()) AND DATEADD(hh, - 1, GETDATE())) AND (accepteddate IS NULL)

GO

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 Discussions

Related Forums