April 11, 2007 at 3:34 pm
Hi,
I am trying to get the JOB_ID or the JOB_NAME from within a step in a job. I can get the JOB_ID from the program_name from running a sp_who2, but that is in binary form and I can not convert that properly to use it to select the JOB_ID from the sysjobs table.
What I am trying to do is have a stored proc that I can call from every job and obtain information from the sysjobhistory table from a previous step of that some execution of the job. Right now, if a step fails to goes to a “send email step” and sends a message with the job name, hard coded, without details about why it failed. I want to include the messages column from the sysjobhistory table for the failed step without hard coding anything. I just can not get the JOB_ID of the current job.
I am running SQL 2000 SP4, and SQL 2005 SP2.
Any thoughts?
April 11, 2007 at 4:48 pm
Brian,
sp_who2 gets program_name from sysprocesses, so join sysprocesses with sysjobs after converting the binary value. I use this query in a "send email" step of a job that checks for long-running jobs:
select RTrim(J.Name),last_batch
from msdb..sysjobs J with (nolock)
inner join master..sysprocesses RP with (nolock)
on RP.program_name like ''SQLAgent - TSQL JobStep (Job '' + master.dbo.fn_varbintohexstr(convert(binary(16),J.job_id )) + ''%''
where datediff(hour,last_batch,getdate()) > 1
Greg
Greg
April 11, 2007 at 11:15 pm
This is easily done using SQL Server Agent Tokens (have a look at sp_add_jobstep in Books Online).
In your job step, you can get the job name using the token [JOBID]
e.g.
Declare @JobNamevarchar (100)
Select @JobName = Name
From msdb..sysjobs j
Where job_id = [JOBID]
You will get a warning saying the JOBID is not a column (in sysjobs). This is OK because SQL Server Agent will actually replace the string [JOBID] with the actual id of the job when the job runs. You just need to add your error handling logic after the above SQL.
April 12, 2007 at 7:33 am
Happycat59's answer is a good one. Remember when you move to SQL 2005 that the format of Agent Tokens changes. The SQL 2005 BOL has the details, and also look at KB 915845.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 12, 2007 at 8:53 am
I agree, the token was exactly what I was looking for.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply