Missing Target Local Server in Job

All we need is an easy explanation of the problem, so here it is.

SQL Server 2016 Enterprise, CU15 + GDR.

Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn’t add the Local Server as the target. This prevents the job from executing.

Missing Target Local Server in Job

Is there anyway to determine if a specific job doesn’t have any target server defined as is the case above?

Thanks,
Craig

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Selecting all jobs that have an enabled schedule but no next run date.

SELECT
    sj.name AS job_name,
    sjs.next_run_date,
    syss.enabled AS schedule_enabled
  FROM msdb.dbo.sysjobs sj
  INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id 
  INNER JOIN msdb.dbo.sysschedules syss ON sjs.schedule_id = syss.schedule_id 
  WHERE 
  1=1
 AND  sjs.next_run_date = 0
 AND syss.enabled = 1

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply