Hi all, How do we prevent a job not to run more than once?
I have a job that only need to run one time a day. If I
accidentally run again then I have an issue. Duplicate
data. Thanks All

Don't schedule it to run more than once and then secure your system so that
no unauthorised users can start jobs (that they do not own).

You could add additional logic within you process or as another job step to
perform various checks.
You could query the sysjobhistory table to check when a job last ran for
example and if within a specified time period, raise an error halting the
job. A better method would be to design your processing such that it can
cope with multiple runs, or failing that checks for invalid data or other
such validations as can be determined by your business logic, and raiser
errors appropriately.




--
Darren Green
http://www.sqldts.com

"hngo01" <hngo01 (AT) hotmail (DOT) com> wrote

There are a couple of things that you can do:

a) The best thing is to introduce some kind of checks in your process. You
mentioned that if the job runs more than once, you get duplicate data. What
you can do is - although it might be lengtheir - create an excel file with
all the data first and store it in a centralized location. Whenever, that
excel file is found to be already existing, make the job fail. This way,
you will not be able to put another set of duplicate data into your table,
even if you run the job manually.

b) If you want, you can write a Stored Procedure which also has a business
logic built into it. The Sp will not only run the job, but will also keep a
log file of the last time the job was run, along with the status -
failure/success. The next time when the Sp runs, it will first check the
log file and based on the status, can either run the job or cancel it.

Just some of the ways to counter your problem.


sanchans (AT) online (DOT) microsoft.com

This posting is provided "AS IS" with no warranties, and confers no rights.