I have a job:
RunProcedures
It has steps:
Step 1: Do Something
Step 2: Do something Else
Step 3: Email
In step 3, I have:
EXEC spSendSuccessEmail -- and here's where I want to pass the job name.
Here's the code for the stored procedure above:
ALTER PROCEDURE [dbo].[spSendSuccessEmail] @JobName VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EmailBody VARCHAR(50)
SET @EmailBody = @JobName + ' ran successfully'
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBTeam',
@recipients = 'user@universe.com',
@copy_recipients = 'user2@universe.com',
@subject = 'Process Complete',
@body = @EmailBody,
@importance = 'Normal',
@sensitivity = 'Normal';
END TRY
BEGIN CATCH
EXEC spGetDatabaseErrorInfo
END CATCH
END
How do I pass to this stored procedure the name of the job it's in?
I read on tokens but I'm a little confused on how to accomplish this task.
Can anyone give me a hand?
UPDATE I did some testing and for some versions of SQL Server(2005 sp1 and beyond), you'll have to escape the Token. I've added the escape command to the code below.
$(JOBID) is the token you want to use, and you can query from msdb..sysjobs to find the job name.
For sql server 2005 sp1 and beyond:
declare @jobName varchar(100)
select @jobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))
exec spSendSuccessEamil @jobName
For sql server 2005 and earlier:
declare @jobName varchar(100)
select @jobName = name from msdb..sysjobs where job_id = $(JOBID)
exec spSendSuccessEamil @jobName
Just use one of these as your command text within the 3rd step of your job.