Agent Tokens in Job Steps: Job Name, Step and Start Time

Agent tokens let a job step describe itself: its job name, step number and start time. Copy the job and the log text stays correct, because nothing is hard-coded. One rule matters most: escape the text before it lands inside a T-SQL string.

Why hard-coded job names go stale

You build a nice job with a logging step. It writes “Nightly Load, step 3” into a table. Then someone copies the job for the test server and renames it. The copy cheerfully logs “Nightly Load” forever. The log lies and nobody notices until the 2 AM page.

Tokens fix this. You write a placeholder such as $(ESCAPE_SQUOTE(JOBNAME)) in the step. When the job runs, Agent swaps in the real job name before SQL Server sees the text. A copied job reports its own name.

Tokens only work inside a job step

This surprises people. Paste a token into a normal query window and nothing is replaced. The first column below returns the placeholder itself. TRY_CONVERT cannot turn it into a number, so the second column returns NULL.

One oddity in my code. Tools such as sqlcmd treat a dollar sign followed by a parenthesis as their own variable and mangle it. So the scripts here type # and swap it for the dollar sign. In SSMS you can type the token directly.

DECLARE @JobToken nvarchar(100) = REPLACE(N'#(ESCAPE_SQUOTE(JOBNAME))', N'#', NCHAR(36));
DECLARE @StepToken nvarchar(100) = REPLACE(N'#(ESCAPE_SQUOTE(STEPID))', N'#', NCHAR(36));

SELECT @JobToken AS JobNameInQueryWindow,
       TRY_CONVERT(int, @StepToken) AS StepIdInQueryWindow;

Build a job that logs itself

Now a real job. It has one T-SQL step that inserts its own job name, step id and start date and time into a log table. The step id goes in as text and is converted to a number after the swap.

The demo creates a database named SqlAuthorityDemo and jobs in msdb, which are server-level objects. The last block deletes all of them. First the database and the log table.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
CREATE TABLE dbo.JobTokenLog
(
    LogId        int IDENTITY PRIMARY KEY,
    JobName      nvarchar(128) NOT NULL,
    StepId       int           NOT NULL,
    JobStartDate char(8)       NOT NULL,
    JobStartTime varchar(6)    NOT NULL,
    LoggedAt     datetime2     NOT NULL DEFAULT SYSDATETIME()
);

Next, a small helper. It starts a job, waits for it to finish, and prints that run’s history rows. It checks every second and gives up after 60 seconds.

CREATE OR ALTER PROCEDURE dbo.RunJobAndWait @JobName sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @JobId uniqueidentifier = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName);
    DECLARE @LastId int = (SELECT ISNULL(MAX(instance_id), 0) FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId);
    DECLARE @Seconds int = 0;

    EXEC msdb.dbo.sp_start_job @job_id = @JobId;
    WHILE @Seconds < 60 AND NOT EXISTS
          (SELECT 1 FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId AND step_id = 0 AND instance_id > @LastId)
    BEGIN
        WAITFOR DELAY '00:00:01';
        SET @Seconds += 1;
    END;

    SELECT h.step_id, h.step_name, h.run_status,
           CASE WHEN h.step_id = 0 THEN LEFT(h.message, CHARINDEX(N'.', h.message))
                ELSE SUBSTRING(h.message, CHARINDEX(N'. ', h.message) + 2, 60) END AS message
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.job_id = @JobId AND h.instance_id > @LastId
    ORDER BY h.instance_id;
END;

Now the job. Each token is wrapped in ESCAPE_SQUOTE. Look at what msdb stored: the command still contains the tokens, untouched. The swap happens only at run time, in memory.

EXEC msdb.dbo.sp_add_job @job_name = N'DemoJobToken';

DECLARE @Command nvarchar(max) = REPLACE(
N'INSERT dbo.JobTokenLog (JobName, StepId, JobStartDate, JobStartTime)
VALUES (N''#(ESCAPE_SQUOTE(JOBNAME))'', CONVERT(int, ''#(ESCAPE_SQUOTE(STEPID))''),
        ''#(ESCAPE_SQUOTE(STRTDT))'', ''#(ESCAPE_SQUOTE(STRTTM))'');',
N'#(', NCHAR(36) + N'(');

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoJobToken', @step_name = N'Write log row',
     @subsystem = N'TSQL', @database_name = N'SqlAuthorityDemo', @command = @Command;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoJobToken', @server_name = N'(local)';

SELECT s.step_id, s.step_name, s.command
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE j.name = N'DemoJobToken';

Run it and read the log

Start the job. The first result is the step history: step 1 succeeded. The second is the log table. The job name, the step id and the start date and time are real values, and not one of them was typed into the step. The time reads as hhmmss, so a value like 105933 means 10:59:33.

EXEC dbo.RunJobAndWait N'DemoJobToken';

SELECT LogId, JobName, StepId, JobStartDate, JobStartTime
FROM dbo.JobTokenLog
ORDER BY LogId;

The start date and time belong to the job, not to the step. If you need the moment a later step ran, take it inside the step. The LoggedAt column does that with SYSDATETIME().

Copy the job, and break it on purpose

Now the 2 AM scenario. Copy the step into a job with a different name, and give the name an apostrophe: DemoJobToken O’Brien. Same command, no edits. The second log row carries the new name, apostrophe intact.

DECLARE @Command nvarchar(max) =
    (SELECT s.command FROM msdb.dbo.sysjobsteps AS s
     JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
     WHERE j.name = N'DemoJobToken');

EXEC msdb.dbo.sp_add_job @job_name = N'DemoJobToken O''Brien';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoJobToken O''Brien', @step_name = N'Write log row',
     @subsystem = N'TSQL', @database_name = N'SqlAuthorityDemo', @command = @Command;
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoJobToken O''Brien', @server_name = N'(local)';

EXEC dbo.RunJobAndWait N'DemoJobToken O''Brien';

SELECT LogId, JobName, StepId, JobStartDate, JobStartTime
FROM dbo.JobTokenLog
ORDER BY LogId;

Why the escape matters: Agent does a plain text swap, like find and replace. ESCAPE_NONE swaps the raw name in, so the apostrophe ends the string early. Here I switch the copy to ESCAPE_NONE. The step fails with error 102, and the log table gets no third row.

DECLARE @Command nvarchar(max) =
    (SELECT s.command FROM msdb.dbo.sysjobsteps AS s
     JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
     WHERE j.name = N'DemoJobToken O''Brien');

SET @Command = REPLACE(@Command, N'ESCAPE_SQUOTE', N'ESCAPE_NONE');
EXEC msdb.dbo.sp_update_jobstep @job_name = N'DemoJobToken O''Brien', @step_id = 1, @command = @Command;

EXEC dbo.RunJobAndWait N'DemoJobToken O''Brien';

SELECT COUNT(*) AS log_rows FROM dbo.JobTokenLog;

One bad character in a name, and the step dies at run time. That is why every token gets ESCAPE_SQUOTE. Clean up when you are done. This deletes both jobs, then the database.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoJobToken';
EXEC msdb.dbo.sp_delete_job @job_name = N'DemoJobToken O''Brien';
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Write the token once, escape it every time, and let the job name itself.

A reusable job log is not a copied name, it is context captured safely.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.