IF EXISTS(SELECT 1 FROM ...) giving unexpected results in a TSQL job step

  • Weird one

    SQL 2008 Standard SP2 on Windows 2003

    The SQL below checks to see if a given job ('Production DB Backup.Check DB Integrity") is running and issues a sp_stop_job command if it is.

    (I know - you shouldn't be stopping an integrity check etc etc ..)

    Anyway, here it is below

    IF EXISTS (

    SELECT 1

    FROM msdb.dbo.sysjobs_view job

    INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id)

    WHERE run_Requested_date IS NOT NULL

    AND stop_execution_date IS NULL

    AND job.NAME LIKE '%Integrity%'

    )

    BEGIN

    SELECT 'here'

    EXEC msdb.dbo.sp_stop_job 'Production DB Backup.Check DB Integrity'

    END

    Works fine when executed in SSMS - as I'd expect, if the job isn't running it does nothing.

    Now, when I create a job and put this in as a Transact-SQL step, the job fails as the sp_stop_job gets fired ... even if the job isn't running !

    It's as thought the IF EXISTS(..) isn't evaluated correctly ?

    Any thoughts folks ?

  • What is the second job called? It doesn't contain '%integrity%' does it?

  • If I recall correctly the [sysjobactivity] table isn't updates dynamicly but is updated every 15 minutes or so.

    I use the code below if i need to get the active job status (start job if not allready running):

    DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL,

    last_run_date INT NOT NULL,

    last_run_time INT NOT NULL,

    next_run_date INT NOT NULL,

    next_run_time INT NOT NULL,

    next_run_schedule_id INT NOT NULL,

    requested_to_run INT NOT NULL, -- BOOL

    request_source INT NOT NULL,

    request_source_id sysname COLLATE database_default NULL,

    running INT NOT NULL, -- BOOL

    current_step INT NOT NULL,

    current_retry_attempt INT NOT NULL,

    job_state INT NOT NULL)

    INSERT INTO @xp_results

    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

    if (SELECT running FROM @xp_results where job_id = 'AAAAA041-AAA6-4191-826A-AAA3A3312807') = 0-- job not active?

    exec msdb.dbo.sp_start_job @job_name = '{job_name}'

    delete from @xp_results

    John Huang has a blog written about this: http://www.sqlnotes.info/2012/01/13/are-jobs-currently-running/#more-1194

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Ed B (7/23/2013)


    What is the second job called? It doesn't contain '%integrity%' does it?

    Ed B, you are as clever as I was dim.

    Great spot and thank you very much !

    HanShi - thanks for the tip !

Viewing 4 posts - 1 through 3 (of 3 total)

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