how to run a Store Proc in sql server at regular interval

ziorrinfotech
ziorrinfotech used Ask the Experts™
on
I am working on a asp.net web site in that i have a need to run a background process.
but i donot want to make the background process in asp.net as
 after i start this process, I will not have any control on the process which i want to have.

So I read that i can call an SP or  may be a Function at regular interval of time in sql server.

this is what i need to do.
I have a table in my sql server and i this table has a column of DataTime.
Now I want to run a code which will check take this DateTime column for each record in the table and Add 20 min into this.
and then will check the result.
if it is more than the current time then that particular record will be deleted from the table.

I need this code to run at a regular interval of time.

how can i do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use Process.Start"Application name"
 to start  background processing from your asp.net code.

You can schedule this asp page in windows task schedular or
 use timer
Commented:
First you need to create a Transact-SQL Job

http://msdn.microsoft.com/en-us/library/ms187910.aspx

After that, you need to schedule the job  and set it to recurring...

http://msdn.microsoft.com/en-us/library/ms191439.aspx

~Ajitha

Author

Commented:
swapneel_d14

Could you please explain the solution a bit more.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

1] Write  your required code "as select . update and  delete in Stored procedure"

2[ then make a call to that  Stored Procedure using Windows From - and use Timer to call it after required interval.

3] or write a DTS packgae on sql server and schedule it. (google "DTS package")

4] Or you can make a call to winodws application from your web application using Proces.Start

Author

Commented:
this is the prob with my, I am trying to avoid creating windows application because my site is hosted on share account.
and I don't have any windows server and just for this one task buying a new windows server is not good.

Anyways thanks for  your time
As Ajitha saisd..create a SQL Server JOb..and Schedule it to run every 15 mins.

Commented:
ziorrinfotech,

Can you let me know why this particular solution was selected and not mine?

Thanks
Ajitha
To be fair..Ajitha should have got the points...Is there a way to transfer the points or change the accepted solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial