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.