WAITFOR

I want to use WAITFOR to execute one of our sp. Before that I would like to know the experience of all forum GURU's. Whether it is reliable?


Surendra Kalekar

Its reliable, in that it will wait for the period you ask it to.

But I wouldnt recommend it, remember this period could also be extending the duration for which the stored proc is holding locks, you need to be very careful.

Could I ask why you want to use it? Perhaps theres another way

quote:Originally posted by Chappy

Its reliable, in that it will wait for the period you ask it to.

But I wouldnt recommend it, remember this period could also be extending the duration for which the stored proc is holding locks, you need to be very careful.

Could I ask why you want to use it? Perhaps theres another way

The particular sp will execute in the midnight to update a particular column of the table. It#%92s a very small sp which will update few thousand records. This sp will be used only for this task.


Surendra Kalekar

quote:Originally posted by Madhivanan

Then why dont you schedule it as a job?

Madhivanan

Failing to plan is Planning to fail

Do you mean scheduled job will be better solution than WAITFOR?



Surendra Kalekar

Yes, without a doubt, a job is better than WAITFOR <img src='/community/emoticons/emotion-1.gif' alt='' /><br />

I suppose I should say why..

With a job you get all the extra features for free, such as 'retry N times' if job fails.
You also get an audit trail in the job history, error reporting such as ability to email you when it fails etc

SQL Agent runs as a service, its more reliable than leaving a single sql process/connection running, using WAITFOR. It also scales better in the future if you have 100 jobs, rather than 100 processes all doing WAITFOR

Ok thanks Paul and Madhivanan I will go for Job then.


Surendra Kalekar