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

Question

Post reply

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 ¤

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

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 ¤