# >I have a Stored Procedure that must be runnig for all day long everyday.
# >Which is the best way?
# I would be inclined to schedule and call your stored
# procedure from Unix. The danger here is if one invocation hasn't
# completed when the next one starts. You'd need to determine whether
# or not that matters to you or put code in the sp to detect that
Well, for every advantage, there's a disadvantage:)
The problem with the "waitfor" method is that it's OK only if it indeed
lives forever. But the proc could die for a number of reasons:
- It could be a deadlock victim.
- The table accessed by the proc changed schema (e.g., a new
index is created for the table while the proc is running.)
- The proc updates a record, and the update fails.
- Some DBA simply killed the proc unintentionally.
Those are not too unlikely for a "long live" proc.
So if you use waitfor to loop forever, you probably still need
a periodic cron job to see whether the proc is still alive. Therefore
we are back to the crontab solution after all.
Q Vincent Yin | Repeat
(604) 876-9096 (H) | Until 0 = 1;