Executing SQL Query after particular point iof Time

Question

hi all,

In our project we have requirement that, after receiving sms message from third party service provider exactly after 3 minutes i need to execute a sql query to update database.

how should i do this , any suggestions are accepted.

** is it possible using stored procedures...

Exactly the scenario is , we are having mediator service application consider it has webservice. from my application when i send a SMS to webservice application via to SMS service provider , this system will push to embedded device , this will make the system to ON. and after 2 min device will send SMS message to pur application thru SMS service provider to say that checking is going on... after receiving this message exactly i need to update database saying that chekcing done succefully. why 3 minutes because exactly after this time device will go Off.

regards, Mahesh

Answer 1

How exactly are you receiving the SMS Messages. What service is picking them up.

Theres a couple of ways you could do it.

In your Application Code on the SMSReceived Event you could kick off a separate thread to sleep for 180 seconds, and then call your SQL Code.

If you want to do it in the database, you'll need some sort of polling thread.

Record the "TimeReceived" and have a bit flag "PostProcessed"

Then you could just have a job that runs every 60 seconds that goes

SELECT *
FROM ReceivedSMS
WHERE TimeRecieved < dateadd(second, -180, getdatE()) AND
      PostProcessed = 0

FOREACH Record - Execute SPROC & Update PostProcessed = 1.

Answer 2

You can use "SQL jobs" Look at these articles: http://msdn.microsoft.com/en-us/library/ms187910.aspx

http://dacosta9.wordpress.com/2008/10/22/how-to-disable-or-enable-a-sql-server-job-programatically/

Hope this helps.

Answer 3

Run the stored proc immediately but the first T-SQL line is WAITFOR DELAY '00:03:00' ?