tricky question... getting the job name to appear in the job step.

Question

tricky question... getting the job name to appear in the job step.

neotokyo

SSCertifiable

Points: 7239
More actions
July 31, 2006 at 12:04 pm

#60679

can you create a step which knows the job name thats running it?
for example:
say i'm a step, and i want to know what job
i exist in... i then pass the name into a notification script
that i'm executing.
it's tricky, but i believe it can be done. but how??
the only 2 table that 'might' be useful in this are
sysjobs & sysjobsteps
thoughts?
_________________________

Viewing 15 posts - 1 through 14 (of 14 total)

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

happycat59 One Orange Chip Points: 29388 More actions · Answer 1

Have a look in BOL at sp_add_jobstep. In particular, the description of the values you may use for @command includes a table that lists a number of tokens that you can use within the command. One of these is [JOBID] - using this, you should be able to find the name of the job from sysjobs.

Lance Sanders SSC Eights! Points: 903 More actions · Answer 2

On 2000, if your job step is a T-SQL step, the job_id appears in the master..sysprocesses.program_name column for the current @@spid. You'll have to parse it, and convert the string representation of a binary(16) to an actual binary type or uuid to join to msdb..sysjobs, but it can be done.

EdVassie SSC Guru Points: 60464 More actions · Answer 3

Google 'sql jobid sysjobs' and look at the hit from sqldev.net. This looks like having the code you need.

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

neotokyo SSCertifiable Points: 7239 More actions · Answer 4

i'm checking it out now... i'll see what i can do

with this:

http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm

thanks for all the replies. i hope i can get this in play.

cheers!

_________________________

Lance Sanders SSC Eights! Points: 903 More actions · Answer 5

Nice find Ed. It's even in BOL under sp_add_jobstep. How've I missed that?

neotokyo SSCertifiable Points: 7239 More actions · Answer 6

yes, but there isn't any real good example of the token use in there.

sure they exist; but at least give me some thing to work with which

bol isn't that great for.

i'm using the following as reference.

http://msdn2.microsoft.com/en-us/library/ms175575.aspx

_________________________

neotokyo SSCertifiable Points: 7239 More actions · Answer 7

i'm still having trouble getting the id transformed

into the actual name, then passing that name into an smtp notification.

the main goal here is to create a step (which really is an addition) to

a larger set of steps. once the notifiy kicks off i have an @jobname

variable basiclly using the agent token poped into the mail subject

line so you know what job failed.

thats it, but it's tough cause i can't seem to get the job name

from within the step.

_________________________

Lance Sanders SSC Eights! Points: 903 More actions · Answer 8

How are you kicking off the smtp mail? Via stored proc?

declare @jobname varchar(1000)
select @jobname = name from msdb.dbo.sysjobs where job_id = [JOBID]
print @jobname
exec sp_my_admin_email @jobfailed = @jobname

neotokyo SSCertifiable Points: 7239 More actions · Answer 9

no... i basically have the same thing you

got here, except my smtp is just below this script.

it should technically work fine, but i'm still

getting the following error.

error: Invalid column name 'JOBID'

this should work considering it's using the correct agent-token.

thoughts?

_________________________

Lance Sanders SSC Eights! Points: 903 More actions · Answer 10

Lance Sanders

SSC Eights!

Points: 903

August 2, 2006 at 7:08 pm

#652971

Can you script your job & post it?

neotokyo SSCertifiable Points: 7239 More actions · Answer 11

i have since dropped the job while experimenting, but i do want

to complete this soon.

i'll post the script later this afternoon.... doing some other

business with reporting services this morning.

in the mean time... if you have a working job which

presents captures the job name in some form of output like a .txt file

i would be more than interested to see it.

thanks for all your help by the way!

_________________________

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 12

I also had some difficulty finding the solution to this problem, not helped by [JOBID] only working in SQL 2000, not in SQL 2005.

See link (http://msdn2.microsoft.com/en-us/library/ms175575.aspx or search for 'Using Tokens in Job Steps' in SQL 2005 BOL) for how to update SQL 2000 tokens to SQL 2005.

I didn't find the above particularly useful but it may help someone else.

Anyway, this is how I got the job name:-

DECLARE @lnJobId binary(16), @lcJobName sysname

SELECT @lnJobId = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

SELECT @lcJobName = [name]

FROM msdb.dbo.sysjobs

WHERE job_id = @lnJobId

SELECT @lcJobName [@lcJobName]

neotokyo SSCertifiable Points: 7239 More actions · Answer 13

excellent post, but just tried this, and having some issues.

don't suppose you can script out the job in it's entirety?

i would like to see the whole thing if thats ok.

_________________________

MohammedU SSCoach Points: 19644 More actions · Answer 14

declare @jobid sql_variant

SELECT

@jobid = convert(sql_variant,

Substring(program_name, charindex('(job ', program_name)+5 , --50)

(charindex(':', program_name)-1 )- (charindex('(job ', program_name)+5))

)

from master.dbo.sysprocesses (nolock)

where spid= @@spid

SELECT @jobid

MohammedU
Microsoft SQL Server MVP