Obtain Job Name through t-sql

Question

Post reply

Obtain Job Name through t-sql

S. Kusen

SSChampion

Points: 10936
More actions
May 17, 2007 at 7:19 am

#174345

Hi all,
Does anyone know if it's possible to get a current job's name through a t-sql job step?
I'm trying to write to the application event log using xp_eventlog , 'message', 'log_type'
for example, a job has 2 steps, step 1 executes a command, and step 2 executes when step 1 fails and writes to the event log.
I am looking to do this in order to separate low-priority jobs from high-priority.
Ideally, I am looking to do something like the following:
DECLARE @message varchar(255)
SET @message = 'Critical job failure: ' + @@servername + ' - ' +
EXEC xp_eventlog 50001, @message, 'ERROR'
I want to do it this way in order to incorporate Concord's ability to poll the application event log
However, I'd like to have the script be dynamic to obtain the job name, if possible.
Thanks for any help / suggestions.
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 1

you can use @@SPID and get the job name from the master.dbo.sysprocesses.program_name column.

If you can parse that value out you can query msdb.dbo.sysjobsview to get the job name

S. Kusen SSChampion Points: 10936 More actions · Answer 2

Sorry, didn't replace [job_id] with [name] like I said:

SELECT [name]

FROM msdb.dbo.sysjobs

WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

S. Kusen SSChampion Points: 10936 More actions · Answer 3

Thanks for the reply. I'll take a look at that. I was scouring the internet yesterday and came upon the topic of SQL Agent Tokens which I think will do exactly what I need.

In 2005, you can put $(ESCAPE_NONE(JOBID)) to be a token that will have the current job's job ID pulled from msdb.

So, the following query will return the job_id from msdb.dbo.sysjobs:

SELECT [job_id]

FROM msdb.dbo.sysjobs

WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

So, in turn, I can just replace [job_id] with [name] to get the job's name:

SELECT [job_id]

FROM msdb.dbo.sysjobs

WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

The SQL Agent Tokens are something to be looked at as there are many other tokens aside from JOBID that could be useful to many.