March 9, 2009 at 10:49 am
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
March 9, 2009 at 11:46 am
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?
March 9, 2009 at 1:09 pm
Doesn't that feature require a MAPI client like Outlook to be installed?
March 9, 2009 at 1:20 pm
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.
March 9, 2009 at 1:50 pm
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?
April 1, 2009 at 4:53 am
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.
April 2, 2009 at 8:48 am
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."
April 2, 2009 at 3:51 pm
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.
March 2, 2010 at 9:31 am
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
March 5, 2010 at 3:06 am
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)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @GUID = @jobID OUT
SELECT @jobName = name
FROM msdb..sysjobs
WHERE job_id = @jobID
cheers
April 12, 2010 at 11:28 am
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
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply