Results 1 to 5 of 5

Thread: Scheduled Jobs

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: 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...

    Code:
    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.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That did it...I think - won't know for sure until tomorrow...

    Code:
    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
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This doesn't seem to want to work as a scheduled job.
    Code:
    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.
    Last edited by RedNeckGeek; 03-15-07 at 10:03.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •