How can a SQL AGent Job command call it''s own JOb ID?

• sunshine-587009

  SSCrazy Eights

  Points: 8981

  More actions

  April 15, 2011 at 12:36 pm

  #236704

  I"m trying to run a sql agent job that would pass it's own id in the stored proc as a parameter. Would anyone know how I can accomplish this please?

  DECLARE @job_id uniqueidentifier

  DECLARE @job_name sysname

  EXEC dbo.sp_add_jobstep @job_id = @job_id, @step_id = 1,

  @step_name = 'usp_job_monitor_job_start', @on_success_action = 3,

  @on_fail_action = 3, @subsystem = 'TSQL', @database_name = 'msdb',

  @command = 'EXEC dbo.usp_job_monitor_job_insert' + @JOB_ID

  or somehow add a select statement to select it's own id instead of @job_id

  (select job_id from sysjobs where @job_name=to self)???

  any help appreciated... thank you!

  ¤ §unshine ¤

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 15, 2011 at 2:42 pm

  #1312360

  You can query msdb.dbo.sysjobs to get the job id if you know the job name.

  SELECT S.job_id FROM msdb.dbo.sysjobs AS S WHERE S.name = 'name'

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• andrewkane17

  SSCertifiable

  Points: 7529

  More actions

  April 15, 2011 at 4:05 pm

  #1312386

  I got this from the interwebs, can't remember where, just used it for a complex SSAS DB backup routine that had to stall cube build jobs if a SASS DB was being backed. Didn't want to have to hard code job names into lots and lots of existing cube build jobs.

  DECLARE@sql NVARCHAR(72),

  @jobID UNIQUEIDENTIFIER,

  @jobName SYSNAME

  SET@sql = 'SET @GUID = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'

  EXECsp_executesql @sql, N'@guid UNIQUEIDENTIFIER OUT', @GUID = @jobID OUT

  SELECT@jobName = name

  FROMmsdb..sysjobs

  WHEREjob_id = @jobID

  Andrew

• sunshine-587009

  SSCrazy Eights

  Points: 8981

  More actions

  April 15, 2011 at 4:51 pm

  #1312392

  I do know the job name(s) however i do not want to open each job to add the step.

  Let's say very simplistic example) I have Job called MonitoringThisJob

  I want to put in a step in this Job that says execuite sp_InsertINtoMSDBTable 'MonitoringThisJob Job ID'

  Without having to hard code the name or the JobID.

  I'm not sure I understand the 2nd response sql but I'll try it!

  Thank you both.

  Please let me know if you have anything else!

  ¤ §unshine ¤