Job did not run according to schedule

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 6, 2011 at 9:46 am

  #149114

  For the life of me I can't find any script that does that and I can't believe I'm the first one to encounter that problem...

  Long story short the agent was shut down for almost 1 day and I need to figure out which jobs to restart and it what sequence.

  I'm doing it manually this time but I'd like a script if anyone has 1 to pass along.

  TIA.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 6, 2011 at 10:40 am

  #1334523

  Uggh, I can empathize. I have always just knocked these cases out manually.

  Do you have any util UDFs in place to help with the INT to DATE/TIME conversions in MSDB? That can ease the pain a bit.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 6, 2011 at 10:44 am

  #1334530

  Nope I don't have that UDF.

  I find it very hard to believe that nobody, ever, tried to solve this with a script.

  The "good" news is that I only have 15-20 jobs and that I can somewhat rebuild the sequence from memory but it's still not a fun day :pinch:.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 6, 2011 at 11:07 am

  #1334551

  Ninja's_RGR'us (6/6/2011)

  ---

  Nope I don't have that UDF.

  USE msdb

  GO

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'dbo.udf_convert_int_date')

  AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )

  DROP FUNCTION dbo.udf_convert_int_date ;

  GO

  CREATE FUNCTION dbo.udf_convert_int_date (@date_in INT)

  RETURNS DATETIME

  AS

  BEGIN

  IF @date_in = 0

  RETURN NULL

  DECLARE @date_out DATETIME

  SET @date_out = CONVERT(DATETIME, CAST(@date_in AS CHAR(8)), 101)

  RETURN @date_out

  END

  GO

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'dbo.udf_convert_int_time_1')

  AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )

  DROP FUNCTION dbo.udf_convert_int_time_1 ;

  GO

  CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT)

  RETURNS VARCHAR(8)

  AS

  BEGIN

  IF @time_in = 0

  RETURN NULL

  DECLARE @time_out VARCHAR(8)

  SELECT @time_out = CASE LEN(@time_in)

  WHEN 6

  THEN LEFT(CAST(@time_in AS VARCHAR(6)), 2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3, 2) + ':'

  + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)

  WHEN 5

  THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)), 1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2, 2) + ':'

  + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)

  WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)), 2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)

  ELSE '00:00:00' --midnight

  END --AS converted_time

  RETURN @time_out

  END

  GO

  SELECT dbo.udf_convert_int_date(next_run_date),

  dbo.udf_convert_int_time_1(next_run_time),

  *

  FROM dbo.sysjobschedules ;

  GO

  I find it very hard to believe that nobody, ever, tried to solve this with a script.

  I am watching this thread intently

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 6, 2011 at 11:11 am

  #1334553

  I know how to do that.... I was under the impression that you also did the time conversion?

  As you might know... I don't run ALL my jobs at midnight FLAT :w00t:.

  Where's the effing delete button! I just saw the rest of your code post :-P.