stored procedures to run at a particular time

Question

0.00/5 (No votes)

See more:

Dear All,

Can anyone please guide me with some solution. Right now I am adding som infomation in a table, lets say as Sampletb. I have some column like Time,Status in Sampletb.

My requirement is if I enter any record in the Sampletb with a date (for ex:27-dec-2013 23:00) in the time column.

On this particular date and time,status of the particular record should be changed to 1....

I tried creating triggers but triggers cannot fire at a particular time..they run only when any record in the table changes or inserted...

After long research on this I found SQL Jobs are the better option for this..

what my thought is I will create a stored procedure to update the status and wants the sqljob to run the procedure at the date and time i mentioned....

Please help me with this...

Posted 26-Dec-13 2:27am

Rakesh Aytha

Add a Solution

Comments

John d. Bartels 26-Dec-13 9:38am

Are you inserting this record by calling a stored procedure? If so, then you could simply do this work in the sproc. If not, have you given thought to simply creating a stored procedure and then calling the sproc to do your insert? Also, the trigger should work on both Inserts and Updates, and then you could achieve the same results using the trigger. So, I just re-read your post, and it seems like you want to update the "Status" column when a Date is inserted into the time column. But, then your post title is "stored procedure to run at a particular time"... this sounds like two different problems. Might I ask why you want the stored procedures to run at a particular time vs. when the record is actually being inserted or updated?

[no name] 26-Dec-13 11:58am

Actually I dont want to update the status of the record when date is inserted..I want to run the stored procedure on the date and time I mentioned..thats bothering me a lot..because as far as i know triggers can be activated only when particular records get modified or deleted or inserted..so if i enter any date lets say on 2nd jan ..the sp must run on second jan and the time associated with the date..
Hope you understand my problem..if so please suggest me...and thanks for ur time to reply my post

ZurdoDev 26-Dec-13 9:52am

Where are you stuck?

[no name] 26-Dec-13 11:20am

I am struck with running scheduled jobs..can u please guid me with this...

ZurdoDev 26-Dec-13 11:25am

But stuck with what? Please be specific.

[no name] 26-Dec-13 11:55am

in creating a scheduled job which takes parameter date and time from the table.....

3 solutions

Solution 1

As you mentioned in your question, you can schedule a job to run the Stored Procedure on any specific time.

Refer the link which shows the same.
http://devwar.blogspot.in/2010/05/schedule-stored-procedure-to-run-in-sql.html[^]

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express[^]

Hope this helps...

Posted 26-Dec-13 4:44am

JoCodes

Updated 26-Dec-13 4:45am

v2

Solution 2

Create an SQL server job[^], create a schedule[^] and then attach the job to the schedule.[^] - so yes, you're heading in the right direction.

Best regards
Espen Harlinn

Posted 26-Dec-13 4:48am

Espen Harlinn

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)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2013-12-26T04:59:00

Solution 3

Quote:
what my thought is I will create a stored procedure to update the status and wants the sqljob to run the procedure at the date and time i mentioned....

Yes do that.

You may need to create SQL Job dynamically while inserting rows to the Sampletb Table.
So, refer Simple way to create a SQL Server Job Using T-SQL[^] for that.

Posted 26-Dec-13 4:59am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

[no name] 26-Dec-13 11:22am

Thanks for your time on this...and if we can dynamically create the sqljob while inserting as u said my problem will be completed...ill refer your link and update the status ...thankyou once again

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 26-Dec-13 11:56am

Most welcome. Please accept the answer and up-vote. :)

Thanks,
Tadit.

[no name] 30-Dec-13 6:52am

THANKS SIR.I GOT THE EXACT SOLUTION WITH UR SUGGESTION...THANKYOU VERYMUCH

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 30-Dec-13 7:05am

Glad to hear that. Most Welcome buddy.

Keep coding. :)

Thanks,
Tadit