Post reply

First run of the job fails ...subsequent scheduled runs work fine

  • August 18, 2011 at 11:56 am

    #246455

    ERROR: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The statement has been terminated. [SQLSTATE 01000] (Error 3621). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.

    I have the below script (to monitor long running jobs) running as a job via an SP call.

    The first run of it FAILS with the above error, but the subsequent scheduled runs of the same job works just fine.

    Any clues to resolving this mystery?

    I have even tried to add the same SP as a second step but the job still fails every morning in its first run but completes fine in other runs all through the day. (PUZZLING!)

    DECLARE @count int

    CREATE TABLE #t

    (

    job_id UNIQUEIDENTIFIER,

    last_run_date CHAR(8),

    last_run_time VARCHAR(8),

    next_run_date CHAR(8),

    next_run_time VARCHAR(8),

    next_run_schedule_id INT,

    rRun INT, rSource INT, rSourceID NVARCHAR(255),

    Running BIT,

    CurrentStep INT,

    RetryAttempt INT,

    [State] TINYINT

    );

    DECLARE @owner SYSNAME;

    DECLARE c CURSOR LOCAL

    FOR

    SELECT DISTINCT SUSER_SNAME(owner_sid)

    FROM msdb.dbo.sysjobs;

    OPEN c;

    FETCH NEXT FROM c INTO @owner;

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    INSERT #t EXEC master.dbo.xp_sqlagent_enum_jobs 1, @owner;

    FETCH NEXT FROM c INTO @owner;

    END

    DEALLOCATE c;

    Truncate table TBLMonitorActiveJobs

    --insert into TBLMonitorActiveJobs

    SELECT DISTINCT

    --t.job_id,

    j.name,

    Duration_Minutes = DATEDIFF(MINUTE, t.StartDate, t.[Now]),

    t.StartDate

    FROM

    (

    SELECT

    job_id,

    StartDate = CONVERT(DATETIME, next_run_date + ' ' + STUFF(STUFF(next_run_time, 5, 0, ':'), 3, 0, ':') ),

    [Now] = CURRENT_TIMESTAMP

    FROM #t

    WHERE Running = 1

    ) t

    INNER JOIN

    msdb.dbo.sysjobs j

    ON

    t.job_id = j.job_id

    WHERE t.StartDate < DATEADD(MINUTE, -5, CURRENT_TIMESTAMP);

    DROP TABLE #t;

    Select * from TBLMonitorActiveJobs

  • August 19, 2011 at 9:35 am

    #1370839

    Anyone?

    Any input?

Viewing 2 posts - 1 through 1 (of 1 total)

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