| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2009-08-04 : 06:44:23
|
| i have an update query (very quick) that I want to run every second (to check one view for new records and to add it to my table)is there a way to do this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 06:50:31
|
No. A job can be run every 10 seconds.You can however have 10 jobs run 1 seconds apart.Why don't you consider a TRIGGER instead?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 07:59:20
|
| how can I set it to 10 seconds?I only have an option of 1 minute as the lowest.Also will this slow down the system? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-08-04 : 08:07:36
|
| This sounds more like a job for a trigger (as Peso already suggested). That way you don't waste time constantly polling a table / view and then deciding to maybe insert or update something.Why not tell us your complete requirement?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 08:13:25
|
| ok here's the issue i have a table in access -- I need to move these records to sql but need it instantaneously.I tried a view opening the access table directly in sql and it works on the server but not from my vb program calling the view. I therefore somehow need to move these records over as they are added.any other suggestions as to the best way to do this? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-08-04 : 08:15:34
|
Can you create a trigger in access to do the work?http://office.microsoft.com/en-us/access/HP030854151033.aspxI'm sorry my access experience is very limited.  Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 08:38:52
|
| that won't work as it's not an .adp file |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 08:41:14
|
quote:
Originally posted by esthera
how can I set it to 10 seconds?
You set the schedule for the job to run every 10 seconds.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 09:18:23
|
| in ms management studio the smallest option is a minute - how do I change to less?can I schedule the same job 10 times? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 09:25:51
|
1. Go to the SCHEDULES tab2. Click New3. In the Daily frequence section, click "Occurs Every"4. Change to "Seconds" in the drop-down listDone.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 09:27:05
|
| i only have hourly and minutes |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 09:29:36
|
I assume you are using SQL Server 2005, since this forum is for SQL Server 2005.SQL Server 2000 have only Hours and Minutes.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 09:43:38
|
| it is 2005 and i only see hours and minutes |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-08-04 : 09:54:58
|
quote:
Originally posted by Peso
I assume you are using SQL Server 2005, since this forum is for SQL Server 2005.SQL Server 2000 have only Hours and Minutes.
N 56°04'39.26"E 12°55'05.63"
It is available from 2008 onwardsMadhivananFailing to plan is Planning to fail |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 09:55:21
|
| so is the only way to schedule it 60 times? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-08-04 : 09:55:43
|
| <<I tried a view opening the access table directly in sql and it works on the server but not from my vb program calling the view.>>What is the problem you are having?MadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 10:39:41
|
No, you can't have a job started twice.If the job is running when the second schedule fires, the execution will be aborted.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 10:41:16
|
| so I have to create 60 jobs for this?any better way of doing this? |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-04 : 10:49:13
|
| I have an idea.If you can, why not migrate the tables to SQL server and then change the access DB so that it uses SQL server rather than access tables? I've done this several times, it makes it easier to help the user and they don't have many of the issues associated with MS Access. I've even done it without the users being aware that their data was really in SQL server. MS Access can be a nice little front end if you're short on GUI developers.An infinite universe is the ultimate cartesian product. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2009-08-04 : 10:51:15
|
| that's what I want to do but I can't do yet - I need to wait for client to update software that inserts the record in. |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-04 : 11:03:51
|
| Does the client software update the access database directly or through a form?If it's directly then it might be possible to change the connection string the client software uses. Then you can possibly make the switch without waiting.If it's in an access form then you can write a procedure in the form to update the SQL server database.An infinite universe is the ultimate cartesian product. |
|
|
|
Next Page
|
run a job every second