16

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

Community
  • 1
CleanFill
  • 531
  • 1
  • 3
  • 9

5 Answers5

24

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))) ;
Marian
  • 15,741
  • 2
  • 62
  • 75
2

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
CleanFill
  • 531
  • 1
  • 3
  • 9
2

Check this:

DECLARE @jobId BINARY(16)

SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs
WHERE name = 'Your_Job_Name'
Michael Green
  • 25,255
  • 13
  • 54
  • 100
Gerben Kolkman
  • 21
  • 1
0

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

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

Ljuba Buinjac
  • 11
  • 3
-3
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
AND 
(SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(a.PROGRAM_NAME,30,10)
Paul White
  • 94,921
  • 30
  • 437
  • 687
Virendra Agrawal
  • 1