Sql-server – Get Job id OR Job name from within executing job

powershellsql-server-2008sql-server-2008-r2

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at that time, etc. The info is then exposed via SSRS so we can see an job failures / long running jobs / & jobs that haven't run but should have(or if someone messed with a schedule).

To do this, I have 2 job steps added into each job on every server and I would like to reduce the script down to just 1 step added to each job..possibly even call it from a network share..

But my issue is one of the 3 parameters I pass. I need to get the executing jobid or job name from within the executing job so I don't have to hardcode the name parameter.
The 3 parameters I pass are jobid, status(success/fail), errormsg. The powershell script I wrote is pretty straightforward.

Invoke-sqlcmd -ServerInstance "MYRemoteSYSTEM" -Database remoteDB -Query "exec dbo.JOB_LOG 'JOBNAME/ID','Success/FAIL','BAD MESSAGE HERE'"

This writes what I need to the table. I've looked at
msdb.dbo.sp_help_job /
msdb.dbo.sp_get_composite_job_info /
dbo.xp_sqlagent_enum_jobs /
but none of these will guarantee I get the ID or name of the correct executing job in the event that there are more than 1 jobs executing at the same time.

I've even tried looked at sys.sysprocesses but I think since the agent job is a powershell script, it shows as ".Net SqlClient Data Provider" so I can't trim the binary JOBID off the jobs that show as "SQLAgent – TSQL JobStep (Job 0xF1800243164745429C30474FFD5C990C : Step 1)"
—this I learned from Denny cherry's post – thanks denny-

Any ideas on how to grab the executing jobid would be greatly appreciated.

Thanks,

Chris

Best Answer

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.

At the end of the article there's one example with jobid:

SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;