Blog

A useful feature in the SQL Agent is the possibility to use tokens. Tokens are a number of system variables (e.g. $(ESCAPE_SQUOTE(DATE) that contains the current date) that gets expanded/replaced with a value when the job is run. An obvious use of tokens is to create a new log file with today’s date and time in the file name each time an SQL Agent job is run.

For a complete list of tokens, see MSDN.

Removed tokens

Note that, as of SQL Server 2012, the tokens AGENT_JOB_NAME and AGENT_STEP_NAME seems to be removed for some reason.

Alert tokens

Tokens that are prefixed with an A- will not expand unless the job is activated by an alert. Further, these tokens are disabled by default for security reasons. If you call them when they are disabled, the job will fail with a similar error message:

[red_box]
Unable to start execution of step 1 (reason: Variable A-DBN not found). The step failed.
[/red_box]

To enable them, use SQL Server Management Studio. Right click SQL Server Agent, select Properties and select the page Alert System. Check the Replace tokens for all job responses to alerts checkbox:

Alternatively, run:

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1

Note that it may take a while for this change to have effect. If you have the possibility, restart the SQL Agent service to make the change immediately.

Tokens demo

Perhaps the best way to demonstrate tokens, is to create a SQL Agent job that expands all of them. So here it is:

USE msdb
GO

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job
	@job_name = 'Print Tokens Job',
	@enabled = 1,
	@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep
	@job_id = @jobId,
	@step_name = 'Print tokens step',
	@step_id = 1,
	@subsystem = N'TSQL', 
	@command = N'
	PRINT ''*********Alert Tokens*********''
	PRINT ''Database name: $(ESCAPE_SQUOTE(A-DBN))''
	PRINT ''Server name: $(ESCAPE_SQUOTE(A-SVR))''
	PRINT ''Error number: $(ESCAPE_SQUOTE(A-ERR))''
	PRINT ''Error severity: $(ESCAPE_SQUOTE(A-SEV))''
	PRINT ''Error message: $(ESCAPE_SQUOTE(A-MSG))''

	PRINT ''*********General Tokens*********''
	--PRINT ''Agent job name: $(ESCAPE_SQUOTE(AGENT_JOB_NAME))'' --Removed in SQL 2012?
	--PRINT ''Step name: $(ESCAPE_SQUOTE(AGENT_STEP_NAME))'' --Removed in SQL 2012?
	PRINT ''Current Date: $(ESCAPE_SQUOTE(DATE))''
	PRINT ''Instance: $(ESCAPE_SQUOTE(INST))''
	PRINT ''Job Id: $(ESCAPE_SQUOTE(JOBID))''
	PRINT ''Computer name: $(ESCAPE_SQUOTE(MACH))''
	PRINT ''Master SQLServerAgent service name: $(ESCAPE_SQUOTE(MSSA))''
	PRINT ''Prefix for the program used to run CmdExec job steps: $(ESCAPE_SQUOTE(OSCMD))''
	PRINT ''SQL Server installation directory: $(ESCAPE_SQUOTE(SQLDIR))''
	PRINT ''SQL Server error log directory: $(ESCAPE_SQUOTE(SQLLOGDIR))''
	PRINT ''No times the step has executed (ex retries): $(ESCAPE_SQUOTE(STEPCT))''
	PRINT ''Step Id: $(ESCAPE_SQUOTE(STEPID))''
	PRINT ''Computer name: $(ESCAPE_SQUOTE(SRVR))''
	PRINT ''Current Time: $(ESCAPE_SQUOTE(TIME))''
	PRINT ''The time the job began executing: $(ESCAPE_SQUOTE(STRTTM))''
	PRINT ''The date the job began executing: $(ESCAPE_SQUOTE(STRTDT))''

	PRINT ''*********Goodbye!*********''
	', 
	@database_name = N'tempdb',
	@output_file_name = N'$(ESCAPE_SQUOTE(SQLLOGDIR))\Print_Tokens_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt'

EXEC msdb.dbo.sp_add_jobserver
	@job_id = @jobId,
	@server_name = N'(local)'

If you run this job, a log file will be created in the SQL Server error log folder path. The name of the file will be “Print_Tokens_YYYYMMDD_HHMMSS.txt”. The content of the file will look something like this:

The text “SQLSTATE 01000” can be ignored.