| Author |
Topic |
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
 Posted - 2012-01-07 : 16:13:29
|
| Is it possible to update a field based on the server time. For example I have 2 tables.Table1.StartTime = 2012-01-09 21:30:00.000Table2.Process = NullWhen the server time hits 2012-01-09 21:30:00.000 I need Table2.Process to be populated as followsTable2.Process = ProcessAAnd various other process with be updated in that field at other set times. I guess if I can get the first part working this part should follow suit. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-07 : 17:00:35
|
| The simplest I can think of is to set up an agent job and schedule it to run at the specified time. |
 |
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-01-08 : 02:27:00
|
| Thank you for that. I am hesitate to use the SQL Agent as I am not the only one who has access to the server. I have found that stops sometimes when Windows updates are loaded or the server is re-booted for some reason.An alternative is that I do it from vb.net windows app that links to there SQL Server data. But there are a number of reason I don't want to do this. Mainly the use of timers in the program causes problems. So would be better if it could be done on the SQL Server. But I am guessing this is looking unlikely. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-08 : 02:42:10
|
| StartTime is a field of table right? so whenever its value becomes 2012-01-09 21:30:00.000 you want updation to happen? is it linked to some default constraints? what does value designate?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-01-08 : 03:24:12
|
| Yes StartTime is a field in a table. And when server time hits that time I want to be able to update another table with a specified process.This has not been deigned yet so it may well be changed. There could be many processes so I think there should be a StartTime and EndTime for each process so if the server time falls between these times that process identifyer will populate another table showing what the current process is.After this happens a windows application will pick up that field and change the form displayed depending on the type of process that is current. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-08 : 03:44:13
|
| Dunno much about it, but might Service Broker handle this?Having said that you need to make sure that SQL Agent is ALWAYS running, lots of things will (and should!) depend on it - like Backups. So fixing that may be your best answer.But you would then need a scheduled task running every minute to check for any rows that need updating, which may not be very efficient? And would "running ever minute" be a quick enough reaction when a StartTime is exceeded? or would it need to run "to the millisecond" when a row's StartTime was reached? |
|
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-01-08 : 03:48:00
|
| It would need to update by the second. For all users must be using the same screen.As for SQL Agent it stops when they reboot the server. I have told them to let me know when they do this. But they don't. I am not sure if there is a way by default for it start but I think I better leave that for another thread.Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-08 : 04:00:22
|
quote:
Originally posted by ConfusedAgain
It would need to update by the second. For all users must be using the same screen.As for SQL Agent it stops when they reboot the server. I have told them to let me know when they do this. But they don't. I am not sure if there is a way by default for it start but I think I better leave that for another thread.Thanks again.
if you've set a schedule for agent job, then it will get executed automatically only agent is up.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-01-08 : 04:03:32
|
| Ok if what you are saying is the only way to do this is with SQL Agent I think I will have to update it using a Timer in the Windows form. I can't rely on the SQL Agent running all the time.Many thanks. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-08 : 05:06:13
|
quote:
As for SQL Agent it stops when they reboot the server. I have told them to let me know when they do this. But they don't. I am not sure if there is a way by default for it start but I think I better leave that for another thread.
SQL Agent can be set up to start automatically at reboot. If you have any sway with the sysadmins, having them do that would be best. The setting is in services.msc. |
|
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-01-08 : 05:11:45
|
| Ok thanks for that fro future reference. I will look into that. I think I am going to go the way of creating a timer in a Windows form as for this issue. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-08 : 06:27:07
|
| If SQL Agent is not restarting on Reboot the Service is almost certain set to "Manual" start rather than "Automatic" start. That's a simple change for your admins (and, to be honest, I would have expected admins to know / have checked that, so maybe its something more complex). There is also a setting to "automatically restart SQL Agent if it stalls / crashes" which ought to be set too.I don't think your Windows APP is a better solution. It will still be prone to failure, and you have the APP-SQL communication which will be an overhead for the system running every Second.However, if your APP first finds the next-scheduled-StarTime and then does not call SQL until that time that would be very efficient. Perhaps it would need to call SQL every 5 minutes? or every minute? just to check if a record with a newer StartTime has been INSERTED/UPDATED - otherwise if the next StartTime is not until "next year" it would not honour any newer item that is added. The restriction would be that a new StartTime being added which is due to fire in less than N minutes will not be honoured [until the next "check" schedule].But you could mostly do that with SQL Agent too. You could run some SQL code (every second) that just checked what a cached value of "NextStartTime" was, and if that time has passed then run the full-fat SQL to do the processing; that "light-weight" task could also do a check for "Next Scheduled StartTime" every N minutes.Note that you need to process any StartTime that has PASSED - so you need to know which ones have BEEN processed (e.g. by setting a Status column on those rows), this is to safeguard against the server being down / rebooted / missing a one-second-window for any reason, and thus not getting an exact match on a scheduled task.If you use SQL Agent the "cached value" for the next StartTime could be refreshed whenever a record's StarTime is changed. So you add/modify a record's StartTime and a TRIGGER on that table would update the "cached value" with the MIN(StartTime) - which will be the current value, or the value just changed if that is earlier. This would solve the problem compared to your APP route which will periodically need to check if there is an earlier StartTime recently added/modified. |
|
|
|
|
Update record based on server time