Re: Which SQL Agent job has called my sp?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Richard R (general_at_adslleamcres.demon.spamfreeco.uk)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 21:19:49 -0000

Thanks Jasper,

I shall have to wait until tomorrow UK time to try it, but that looks the
business.

"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:uXQXSFtCEHA.2052@TK2MSFTNGP11.phx.gbl...
> In a TSQL job step you can reference a token [JOBID] that returns the
jobid
> and use that to look up the name e.g. in a TSQL job step
>
> exec myproc @jobid = [JOBID]
>
> where myproc looks like
>
> create proc myproc @jobid uniqueidentifier
> as
> declare @job sysname
> select @job = [name] from msdb.dbo.sysjobs
> where job_id = @jobid
>
> print @job
>
> See http://sqldev.net/sqlagent/SQLAgentStepTokens.htm for details.
> Note that the jobstep won't parse with a token in it but it will work :-)
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
>
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
>
> "Richard R" <general@adslleamcres.demon.spamfreeco.uk> wrote in message
> news:O0fB7usCEHA.3348@TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > As part of my error handling process, I have a step in each of my SQL
> Agent
> > jobs which is executed if one of the main steps fails (called by the On
> > Failure property of the step). This calls a stored procedure which does
> > assorted tidying up processes, and then e-mails the administrator to say
> > which step failed. Depending on what's going on, it may then kick off
> > another job as well.
> >
> > Is there anyway I can get this stored procedure to retrieve the name of
> the
> > job which called it?
> >
> > I can obviously pass the job name as a parameter from the job, but
that's
> > too easy. It's also a maintenance headache as there are 30+ jobs in this
> > project.. If I can work out some way of getting the sp to retrieve the
job
> > it is much more maintainable.
> >
> > Thoughts so far:
> > It is possible that more than one job may be executing, so just checking
> for
> > a running job may not be enough.
> > I know the name of the step which calls the job, so can I check which
job
> is
> > executing that step? This is a problem if both jobs fail together, but
> that
> > is unlikely. Is there another way?
> >
> > Thanks in advance
> >
> > --
>
> --------------------------------------------------------------------------
> --
> > -
> > Anti-spam e-mail address. Change before replying
> >
> >
>
>

Relevant Pages