Variable Job Schedule

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Hay - 26 Mar 2008 13:04 GMT

We have a job that needs to run on the 1st working day of the month.
I have a function that returns a bit if the current date is the first
working day of the month. Is there a way to use this function as the
first step of the job and if it returns 1 move to the next step,
otherwise bail out? Can a job kill itself?

I can do it with a separate job, and having the following in the job
step, but I'd prefer not to have multiple jobs.

declare @bit int

set @bit=(select dba.dbo.fn_FirstWorkDay(getdate())) -- for
production
set @bit=(select dba.dbo.fn_FirstWorkDay('2008-01-02')) -- for
testing, fires job

if @bit=1
begin
print @bit
EXEC msdb..sp_start_job @job_name = 'My Job Name Here'
end

TIA!

David Hay

Reply to this Message

Dan Guzman - 26 Mar 2008 13:27 GMT

> I can do it with a separate job, and having the following in the job
> step, but I'd prefer not to have multiple jobs.

Another method is to perform this check in the first job step and throw an
error when it's not the first work day. You can set the step to "quit the
job, reporting success" as the on error action:

IF @bit=1
BEGIN
RAISERROR ('This is not the first workday of the month', 16, 1)
END

The downside to this approach is that if the function raises an unexpected
error, the job will not run regardless of whether or not it is the first
workday and the job will complete with success. A more robust method is to
encapsulate the job step(s) in a DTS/SSIS package and use function to
control conditional workflow.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> We have a job that needs to run on the 1st working day of the month.
> I have a function that returns a bit if the current date is the first
[quoted text clipped - 21 lines]
>
> David Hay

Reply to this Message

David Hay - 26 Mar 2008 13:42 GMT

Dan,

Thanks for the quick advice! I like the DTS/SSIS route better, just
hope my boss does!

Thanks again,

David

Reply to this Message