sql job - sp input dates need to be different in first run

Question

sql job - sp input dates need to be different in first run

sajaff

Grasshopper

Points: 24
More actions
August 26, 2010 at 3:35 am

#387663

I have a sql job which is supposed to execute an SP weekly that queries a table for new entries since the last execution. However the first run of the job needs to query the entire table.
Any ideas on how I can achieve this?
My thoughts so far..
I'm using a query to sysjobhistory that i found on this forum to find the last date of execution. But am unable to figure out how to manipulate this to find if a job has never run.
Since this info is available in the job activity monitor i'm guessing this info is stored somewhere ??

Viewing 4 posts - 1 through 3 (of 3 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If the job has never run then there won't be an entry for it in sysjob history. You will need to change your logic so that it looks through the whole table in such a case.

Alternatively, you could write it so that the stored procedure is independent of the job, and have a table in the database updated every time the proc runs.

John

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

You can use IF Exists to check if the job has ever run before. if the job has never run, there would be no entry in the sysjobhistory table. if there is a record in the table, you can query that to find the last date and time it ran.

or you can use a case statement with left join.

you you can use coalesce(lastexecutiondate, dateifitisBlank) to pass on the parameter to the date variable.

sajaff Grasshopper Points: 24 More actions · Answer 3

Thanks all.

I ended up using the query here http://raoulteeuwen.blogspot.com/2008/09/query-job-last-run-status-in-ms-sql.html

which uses sp_help_job. The status code is 5 for a job that was never run.