Introduction
One of the pretty cool features available in SQL Server Agent is the ability to use tokens for returning values for many system parameters at runtime (for example Server Name, Job Name, Database Name etc).
Recently there was an issue reported in the forums regarding the usage of JOBID token for getting the details of job within job step. This prompted me to analyse more on the feature.
This article explains how you can use the JOBID token to return the job identifier information inside the job step
Illustration
There are quite a few scenarios where we need to use job related information in scripts inside SQL Agent job steps. There are tokens available for this purpose which can be used to return data from job metadata.
One of common use case is when we need to capture the job id for logging purpose inside a step within the same job.
The code can be given as below
DECLARE @jobID varchar(100)
SET @jobID = ‘$(ESCAPE_SQUOTE(JOBID))’
EXEC [dbo].[notifyjobfailure]
@jobID
The notifyjobfailure procedure looks like this
CREATE proc [dbo].[notifyjobfailure]
@jobid varchar(100)
as
insert jobnotify(jobname)
select @jobid
GO
Where jobnotify is a simple table for capturing the job_id along with the current system time.
Include this within step of a job and try executing it. We will find that the job step will fail like below
As seen from the image above, we get a conversion error trying to use the token inside job step.
To understand why this is happening I tweaked the datatype of the column within the table and the parameter to be of type varchar.
So modified code will look like this
ALTER proc [dbo].[notifyjobfailure]
@jobid varchar(100)
as
insert jobnotify(jobname)
select @jobid
GO
Try executing the job and we can see that it executes fine now.
Check the table and we can see the below data populated
Checking the data we can see that the token value gets passed in hexadecimal format rather than as a GUID.
Taking this into consideration, we can tweak the code again to add a conversion step to ensure value gets passed as a valid unique identifier (GUID). Accordingly, the code will look like this
DECLARE @jobID uniqueidentifier
SET @jobID = CONVERT(uniqueidentifier,$(ESCAPE_SQUOTE(JOBID)))
EXEC [dbo].[notifyjobfailure]
@jobID
The procedure code will also be modified as below
CREATE proc [dbo].[notifyjobfailure]
@jobid uniqueidentifieras
insert jobnotify(jobname)
select @jobid
GO
Now go ahead and execute the job and you will find that it executes successfully
Conclusion
Based on the illustration above we can understand the below points
1. The value for JOBID token gets passed as a hexadecimal value
2. While trying to save the value to a table column of type uniqueidentifier always make sure we do an explicit conversion. Otherwise the code will break as seen from the illustration
