Click here to Skip to main content
14,430,374 members
Rate this:
Please Sign up or sign in to vote.
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
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.....
Rate this:
Please Sign up or sign in to vote.

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.
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
   
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
   
Glad to hear that. Most Welcome buddy.

Keep coding. :)

Thanks,
Tadit
Rate this:
Please Sign up or sign in to vote.

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...
   
v2
Rate this:
Please Sign up or sign in to vote.

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100