Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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))) ; 
share|improve this answer
    
+1 Sorry, didn't see your post before I hit enter. –  Aaron Bertrand Feb 17 '12 at 19:50
    
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 Feb 17 '12 at 20:13
    
Aaron, there's no problem if you answered too. The fact that I was 10 seconds faster doesn't mean my answer is better :-). –  Marian Feb 17 '12 at 20:14
1  
Thanks, but meh, it doesn't add much value to have two very similar answers vying for attention span or votes. :-) –  Aaron Bertrand Feb 17 '12 at 21:58

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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