SQL Server Jobs

Question

Post reply

SQL Server Jobs

burahdi

Old Hand

Points: 350
More actions
June 1, 2004 at 2:25 pm

#162600

Hi
Has anyone got a suggession on how to set up a scheduled SQL Agent job to run daily except the last day of the month?
Love to hear from you
Thanks
Dinesh

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I would use an extra step plus this

if dateadd(d,-1,dateadd(m,datediff(m,0,getdate())+1,0))

begin

rsiserror('Last day of month',16,-1)

end

and set the job to on fail mark success on success go to next or something like that.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

What about this (assuming that originally the respective step command was "EXEC some_procedure") :

DECLARE @today datetime

SELECT @today = GETDATE()

IF MONTH(@today) = MONTH(@today + 1)

BEGIN

EXEC some_procedure

END

If it is the last of the month, nothing is executed, but the job continues with next step, as defined in "On success action".

I'm not saying this is better - it is just that I prefer statements that can more easily be understood and managed by colleagues in case I'm away. Also, IMHO it doesn't require a special step added to the job; it can even be used if you just need to skip one of several steps of a job on the last of each month.

HTH, Vladan

burahdi Old Hand Points: 350 More actions · Answer 3

Hi

Thanks for your answers, one question, is it possible for this to be done via the Enterprise manager?

Thanks

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Sure, of course you can. Open your job, then the respective step and enter the SQL into "Command" field - all including DECLARE etc. - it works fine.

burahdi Old Hand Points: 350 More actions · Answer 5

Hi All

The above job failed as the step type is set to operating system command which executes the scheduled DTS package. It appears that the job got confused with the TSQL commands and the operating system commands.

So basically what I am after is a TSql command to execute the DTS package in this stap.

Here are the Tsql commends in that step:

----------------------------------

DECLARE @today datetime

SELECT @today = getdate()

IF MONTH(@today) = MONTH(@today + 1)

BEGIN

DTSRun /~Z0x747CE2EA8A10888A0D960B7D4C0753C9D1EB

END

Thanks

Dinesh

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Hi Dinesh,

you can't mix both types of command in one step. The way to achieve what you need could be to create two steps - first with T-SQL, second with operating system command. Set the T-SQL step so, that it always fails on last of month, otherwise make sure that it always succeeds. This step will not do anything, it there just to control the flow of execution. Set On success goto next step, on failure either quit or jump to the defined step (Go to step No. - behind the one with your DTS).

Hope this helps, Vladan

BTW, could you please edit your previous post and delete the long chain of letter, or at least break it into several? It is quite hard to read when it's so wide... Thanks

burahdi Old Hand Points: 350 More actions · Answer 7

Hi Val

Thanks for that. Where do I edit the previous posts?? sorry sounding like a complete newbie here.

Cheers

Dinesh

Vladan SSC-Insane Points: 22072 More actions · Answer 8

You're welcome :-). The "Edit" button is in the header of each post, on the right. Just click it, the post will open in edit mode (window is the same as if you are replying, but the message is included). Change the text, or add some, and submit the changes. BTW, if you sometimes should need to delete your post completely - e.g. because of duplicate posting - you have to go to edit mode as described above, and only then look for "Delete" button.

Vladan