Database Scheduled manipulation

I've wrote a Restaurant Management application. I have a Database based on SQL Server 2005 which has one table named OrdersItems. Every 5 minutes I want to read all rows of this table and based on a specific criteria Update some fields.

I don't want to do that in my main application and I prefer to have an Alternative engine to perform this.

Which method is the best method to perform such task ? Also note that this Table (OrdersItems) is under process every time because main application must be always running and 开发者_如何学Goget new Restaurant Orders.

You can create a SQL Server Agent job that does the update every five minutes.

If you are using SQL Server Express edition, you can't use SQL Server Agent because it's only included in the "bigger" versions of SQL Server.
In this case, you can create your jobs manually using batch files and Windows Task Scheduler.

I definitely agree with Christian and dougajmcdonald's points about using SQL Task/ Maintenance. However since you included c# in your tags an alternative is to create a Windows Service.

The benefits of this approach

• Can run on a machine that doesn't have the SQL Server Agent installed (including express editions)
• Can be run outside the context of a user login.
• Has standard stop start pause continue mechanism that's well understood.
• If the service itself fails will likely result in an event log

This answer contains a template for a windows service that periodically gets data and executes. You may simply want to change the DoStuff method to execute a stored procedure

Create a dialog timer and let it activate a stored procedure. This has the advantage of being fully contained inside the database (no external process), it does not require SQL Agent (runs on Express) and is completely crash resilient at the point it will survive database detach/attach and backup/restore operations (the scheduled job will run after recovery on the new restored database).

I would expect a SQL Task / Maintenance plan would be the best for this.

You can set them up for whatever interval you want, specifying a SQL statement, maintenance task etc you want to run.

You can also setup alerts etc if you want to know when it fails for example.

Deploy a cron job on a server with access to the database which is started every 5 minutes and processes your data, using transactions. I see one problem there: If the amount of data to be processed is large, it could quite work more than five minutes.