Execution of Stored Procedure for every half an hour.

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Dear Friends,

I have created a Stored Procedure, I want it to execute for every half an hour.
Kindly any one suggest what can I do.

I use like this. Is this good practice.
while (1=1)
begin

begin try

Exec SP

waitfor delay '00:30:00'

end try
begin catch

end catch

Regards,
AP

Posted 5-Jun-12 4:32am

Arunprasath Natarajan

Updated 5-Jun-12 17:56pm

v2

Add a Solution

6 solutions

Solution 2

You can use the SQL Agent to set up a new job that will fire at a given interval (in your case, every half hour). Here is more information on SQL Automation:

http://msdn.microsoft.com/en-us/library/ms189880(SQL.105).aspx[^]

Posted 5-Jun-12 4:34am

Tim Corey

Comments

Maciej Los 5-Jun-12 15:11pm

Interesting answer, my 5!

Arunprasath Natarajan 5-Jun-12 23:50pm

I use like this. Is this good practice.
while (1=1)
begin
begin try
Exec SP
waitfor delay '00:30:00'

end try
begin catch

end catch

Solution 3

Schedule it on SQL Server Agent
SQL Jobs[^]
Also you can use SQL CMD Utility[^] with Task Scheduler[^]

Similar discussion:
How to run a stored procedure every day[^]

Posted 5-Jun-12 18:05pm

Prasad_Kulkarni

Solution 5

Hi,

First u see what edition u are using for sql server.

Connect to the server

SQL Server Agent - create a new job

In the General Tab fill the database name, owner, category -Database Management

Schedules - Give the name for schedule

Schedule type - Recurring and fill other details as ur need

SQL server Agent should be present in ur sql, it does not support in sql express edition

Posted 5-Jun-12 22:34pm

devsuja

Comments

AmitGajjar 6-Jun-12 5:30am

5+ good. but need formatting in your answer.

Solution 4

Hi,

Create a sql sever job using sql server agent.

Create a procedure in which what query to be updated.

Include this procedure in a job and create a schedule for every half an hour.

Its better to use a procedure inside the job. Since updations can only be done in a procedure itself.

Posted 5-Jun-12 21:29pm

devsuja

Comments

Arunprasath Natarajan 6-Jun-12 3:47am

Sorry to say. I dont know how to create a Job using sql server agent.
Can you give me flow please.

Solution 6

http://msdn.microsoft.com/en-us/library/ms187358.aspx[^]

http://msdn.microsoft.com/en-us/library/ms182079.aspx[^]

http://msdn.microsoft.com/en-us/library/ms187320.aspx[^]

Detailed view

Posted 5-Jun-12 23:39pm

devsuja

Updated 5-Jun-12 23:40pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bbirajdar · Accepted Answer · 2012-06-05T04:34:00

Solution 1

You need to create a SQL server job which will execute periodically

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

Posted 5-Jun-12 4:34am

bbirajdar

Comments

Maciej Los 5-Jun-12 15:11pm

Interesting answer, my 5!

Oshtri Deka 5-Jun-12 16:22pm

I recommend this approach.

Arunprasath Natarajan 5-Jun-12 23:49pm

I use like this. Is this good practice.
while (1=1)
begin

begin try

Exec SP

waitfor delay '00:30:00'

end try
begin catch

end catch

krumia 6-Jun-12 7:30am

I'm not really familiar with SQL server, but I can think of few reasons why this is bad.
1. If there is a maximum execution time limit, this script is going to fail after that time limit.
2. You will have a separate permanent process/thread to execute this script, whereas a batch job will create a process every thirty minutes which will die after your procedure is finished. So doing a batch job is good for performance.