Scheduled Jobs

I have a job that runs hourly every day from 7:00 to 19:00.
I'd like to be able to detect the last run of the day.
The problem is, I may change one (or both) or the scheduled run times,
so I don't want to hard code 19:00 into my detection scheme.

I stumbled across the sysjobsschedules table in the msdb database,
and I think the Next_Run_date and Next_Run_time fields will get me where
I need to be.

I'm trying to build a second job that runs at 10 minutes after the hour, 24 hours a day that will somehow detect whether or not the primary job just finished it's last run of the day, and if so, insert some records into a table.

Here's what I have so far...

DECLARE @.intFlg1 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))
IF @.intFlg1=0
INSERT INTO BLDOFF_INV_DAILY()
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly

The problem with this is that it will append records every hour after the last run until midnight. I only want it to append them once.How about this ... check to see if the date has already been written to your table. If it has not, insert a new row with the date and time you pulled from sysjobschedules. If it does exist, update the row with the time you pulled from sysjobschedules.

That way you will only end up with one row per day, and it will hold your last run time. You can't tell when the last job of the day has run until the day is over ;)|||That did it...I think - won't know for sure until tomorrow...

DECLARE @.intFlg1 INTEGER
DECLARE @.intFlg2 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))

SET @.intFlg2=(SELECT COUNT(*) FROM BLDOFF_INV_DAILY
WHERE prod_plan.mcorron.removetime(BLDDATE)=(
SELECT (CONVERT(DATETIME, CAST(NEXT_RUN_DATE AS CHAR(8)), 102)) AS DTM
FROM MSDB.DBO.SYSJOBSCHEDULES
WHERE [NAME]='PRODPLAN_IMPORTORDERS'))

IF @.intFlg1=0 AND @.intFlg2=0
INSERT INTO BLDOFF_INV_DAILY()
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly

Thanks|||This doesn't seem to want to work as a scheduled job.

DECLARE @.intFlg1 INTEGER
DECLARE @.intFlg2 INTEGER
SET @.intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))

SET @.intFlg2=(SELECT COUNT(*) FROM BLDOFF_INV_DAILY
WHERE prod_plan.dbo.removetime(BLDDATE)=(
SELECT (CONVERT(DATETIME, CAST(NEXT_RUN_DATE AS CHAR(8)), 102)) AS DTM
FROM MSDB.DBO.SYSJOBSCHEDULES
WHERE [NAME]='PRODPLAN_IMPORTORDERS'))

IF @.intFlg1=0 AND @.intFlg2=0
INSERT INTO BLDOFF_INV_DAILY(BLDDATE, PRODUCT, WHSE, QTY)
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly
GO

The job says it ran succesfully as scheduled, but for whatever reason it doesn't insert the rows. If copy it and past it into QA, it inserts ~1100 rows.|||You could use msdb.dbo.sysjobhistory using the job_id from sysjobs table. sysjobhistory has the step (0 = Job outcome), run_date, run_time, and the message column will tell you if the job succeeded or failed. Just get the max runtime for the rundate in question ... careful though ... run_date and run_time are stored as integers.