I have a single step job that executes a stored procedure. I would like get the date of the last successful job execution time so that I can just update a delta instead of the whole set of data.
Right now I have the job setup to run once every day, so I have a default parameter that if it's null I set it to GETDATE() - 1 so I'm still updating a delta but what I'd like to do is set the date to the last successful execution of the job.
exec dbo.usp_UpdateFrom @LastSuccessfulExecutionTime
Current procedure is something like
CREATE PROCEDURE dbo.usp_UpdateFrom
@FromDate datetime = NULL --would like to pass last successful execution time of the job
AS
IF @FromDate IS NULL
SET @FromDate = GETDATE() - 1
-- do stuff
END
The tables you want are sysjobs and sysjobhistory in msdb
. Although be warned! SQL Server only maintains a certain number of records, so if there are too many jobs and the history is not large enough, you will end up with no history.
The following code retrieves the job_id
for the given job name, and queries the history table for the last successfully finished run (i.e. step 0, status 1). As you can see, you have to convert the run time back to a date, as SQL Server stores it in two int columns:
DECLARE @job_id binary(16)
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE (name = N'YourJobName')
SELECT TOP 1
CONVERT(DATETIME, RTRIM(run_date))
+ ((run_time / 10000 * 3600)
+ ((run_time % 10000) / 100 * 60)
+ (run_time % 10000) % 100) / (86399.9964) AS run_datetime
, *
FROM
msdb..sysjobhistory sjh
WHERE
sjh.step_id = 0
AND sjh.run_status = 1
AND sjh.job_id = @job_id
ORDER BY
run_datetime DESC