Enumerate SQL Agent job name from job itself

Question

Post reply

Enumerate SQL Agent job name from job itself

Warren Peace

SSCertifiable

Points: 5881
More actions
March 9, 2009 at 10:49 am

#129561

OK, I have a couple of jobs I set up to email after completion/fail. Within the subject and message I put in the SQL Agent Jobs name. Is there any way of getting the current job name that it's running under so I can make this email routine "Universal" for all jobs? I just don't want to keep writing unique routines for every job.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Not that I know of, but I'd be interested to hear if there is a way ...

Why not use the notifications of the agent jobs and just have it take care of it?

Warren Peace SSCertifiable Points: 5881 More actions · Answer 2

Doesn't that feature require a MAPI client like Outlook to be installed?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

For the built in SQL 2000 mail ... yes. You're in a 2005 forum though, so I'm assuming you're on 2005? If so, use database mail to point to your smtp server and setup your operators appropriately.

If you're in 2000 and don't want to do all the nonsense required for SQL Mail to work, there is an alternative xp_smtp_sendmail ... but you wouldn't be able to configure operators to use as notifications at the job level. You'd have to have a step with in the job like you're talking about.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 4

Yes we are on SQL 2005 and yes DMMail is setup.

Do you have a link to an "Idiots guide" on how to setup SQL job notifications?

jason.brown-605015 SSC Enthusiast Points: 119 More actions · Answer 5

Back to the original question, I have a need to know the current context in which a job is running.

What I want to know is when the "current" SQL job started.

I have a particular step that is set to auto-repeat in the case of failure, but want to put in a safeguard in case of false positives.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Check out BOL and search for "Using Tokens in Job Steps" - maybe this will help. This is a feature that has been around since v7.0. However the syntax changed in SQL 2005.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

corsair1 Old Hand Points: 308 More actions · Answer 7

create the following sp:

USE [master]

GO

/****** Object: StoredProcedure [dbo].[usp_job_notification] Script Date: 04/02/2009 16:22:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_job_notification] @job_id uniqueidentifier as

set nocount on

declare @message NVARCHAR(4000)

declare @job_name NVARCHAR(128)

declare @notification_subject NVARCHAR(255)

set @job_name = (select name from msdb.dbo.sysjobs where @job_id = job_id)

set @message = 'Notification of successful job completion of ' + @job_name + ' on ' + @@servername

set @notification_subject = 'Job Success - ' + @job_name

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Database Mail',

@recipients = 'email@yourcompany.com;',

@body = @message,

@subject = @notification_subject;

GO

Then execute the sp using the following command in a job step:

exec master.dbo.usp_job_notification $(ESCAPE_NONE(JOBID))

Then you can take it a step further and pass in a parameter for success or failure and have one stored procedure for both.

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 8

I am also looking into a common job failure step (error message, server, jobname) for Sql jobs.

I am unclear on $(ESCAPE_NONE(JOBID)).

?

thanks

beath SSCrazy Points: 2939 More actions · Answer 9

This is an old thread I know but I was looking for the same thing and came across this code which seems to work perfectly.

DECLARE@SQL NVARCHAR(72),

@jobID UNIQUEIDENTIFIER,

@jobName SYSNAME

SET@SQL = 'SET @GUID = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'

EXECsp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @GUID = @jobID OUT

SELECT@jobName = name

FROMmsdb..sysjobs

WHEREjob_id = @jobID

cheers

rstone SSCertifiable Points: 6209 More actions · Answer 10

It is an old thread, but still a current issue. I have several jobs that log output to a file. I use the Job guid in the file name because I don't know how to get the job name. I can't use T-SQL to get the job name. I could update each step of each job to use the job name in the log file name, but it's easier to use a token. Is there a way to use the job name without having to customize each step? Why isn't there a token for JOBNAME?

Randy

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉