SQL Agent Tips: Using JOBID Token in T-SQL Job Steps To Fetch The Job GUID

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