I am trying to use tokens in my SQL Server agent jobs as described here, but some of the tokens are not working as I expect.
When running an agent job, the tokens which are supposed to expand to provide information such as database name, server name, etc. are not expanding. The result of the token expansion is an empty string instead of the desired value.
Below is a simple example to demonstrate the issue.
A simple job called "Backup" has a single T-SQL step called "Create Backup" which uses the database MyDatabaseName
BACKUP
DATABASE
MyDatabaseName
TO
DISK = N'D:\Backup\"$(ESCAPE_NONE(A-DBN))".bak'
The token $(ESCAPE_NONE(A-DBN)) is supposed to expand to the name of the current database, so I expect this job to produce a backup file with the same name as the database, like this:
D:\Backup\MyDatabaseName.bak
The actual result is the creation of a backup with no name, like this:
D:\Backup.bak
If square brackets are used around the name, the result is an error basically saying the name can't be blank. So the value of the token is seen as an empty string.
If I change the token in this step to (DATE) then it works as expected, and a backup is created with the name being the current date.
D:\Backup\20160821.bak
The box Replace tokens for all job responses to alerts is checked.
I have spent several hours looking for an explanation for this specific behavior, but can't find anything relevant.
OS is Server 2008R2. SQL Server version is 11.0.3128.0
