question

fashraf avatar image
fashraf asked

Creating a Job that checks the time and changes the status.

![alt text][1]I need to create a Job that runs every 5 minutes that and checks two columns in the table .If the current time matches the time in the Column then it should change the inactive status to active and if it is already active then it shouldn't do anything.I am really bad in triggers and jobs so desperately need some help ..Thanks ! [1]: /storage/temp/404-job.png
sql-server-2008job
job.png (15.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
A trigger would be used to act upon an action being taken against that table when it is done, not necessarily being run every 5 minutes. Unless you are referring to a record is updated or touched every 5 minutes by some other process; then if the time matches the value in the column then another column in the table is changed to inactive/active. A trigger could be used in that instance. With a job, if you are referring to a SQL Server Agent job, you would have a T-SQL script that checks every record in the table every 5 minutes. You would have the script go through each record and check the time/date and then change the column to inactive/active based on the check it does. This could work but you would want to consider how many records it has to check every 5 minutes. If you have a large number of records that it ends up taking longer than 5 minutes to complete, you will likely have problems with the job running so often. If you want example scripts you will need to provide a bit more detail on the tables involved and some sample data.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

fashraf avatar image fashraf commented ·
Thanks for the reply ..a script would be really great.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
That's not a trigger situation at all. You should use SQL Agent. That is the built-in scheduling process for SQL Server. You can make it run every 5 minutes by creating a schedule that does that. You just need to write the T-SQL statement that checks the value and does the update. Here's the introductory documentation on [how to use SQL Agent][1]. [1]: http://msdn.microsoft.com/en-us/library/ms189237.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

fashraf avatar image fashraf commented ·
Thank you sir..if its not a bother can you help me with a script please !
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You just need to UPDATE the table with a WHERE clause that is defined by your requirements. What you have tried already?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.