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.



5 Answers 5


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))) ; 
  • I was working with the tokens earlier, but abandoned them because I couldn't get them to work correctly with powershell. I'm debugging now. Thank you both very much.
    – CleanFill
    Commented Feb 17, 2012 at 20:13

Check this:


SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs
WHERE name = 'Your_Job_Name'

To get this to work, I was using the invoke-sqlcmd cmdlet in powershell though the sql agent. Using the information I received above, this what I came up with that worked:

$var = Invoke-sqlcmd -Server "Server\Instance" -Query "select name from msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_SQUOTE(JOBID)))" -- This gives me the job name at runtime

$varname = $var.name -- here i have to put just the name into a variable

Invoke-sqlcmd -Server "server\instance" -Database "remote database" -Query "exec dbo.JOB_LOG $varname,'JOB STATUS HERE','LOG MESSAGE HERE';" 
--here I pass that to the remote system

The one part that got me was that I had to add this line

$varname = $var.name

because if I did not add this, the initial variable $var would pass in the system.data.datatable header long with the column name of the job, so it was causing the variable to mess the query up at run time.

Hope this can help someone else down the road.


Here is how to get job name from uniqueidentifier job id:

SELECT * FROM msdb.dbo.sysjobs WHERE CONVERT(uniqueidentifier, job_id) = 0xA8882784A48A424FABAB00936EC53D06

  • How is this different to the existing answers? Commented Jan 17 at 3:18
  • @RohitGupta , I gave here clear example with real value how to easily get job name using jobid value from sys.sysprocesses, and to get job name workin in it. Commented Jan 18 at 15:20
DECLARE @jobname sysname, @jobid uniqueidentifier

SELECT @jobname=b.name,@jobid=b.job_id  
FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b
WHERE a.session_id=@@spid

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.