SQL Agent Step Tokens  

Home
Up
What's New
DTS
SQL-DMO
SQL-NS
XP's
Misc. Tools
Events
FAQ
Links
About...

 

Using SQL Agent Step Tokens

version: 1.0.0.0

last updated: 15 February 2002

If you have the need to make job steps more machine/instance and/or job independent there is a feature in SQL Agent 7.0 and 2000 called "tokens" that can help you. Tokens work like a pre-processor, before the command text (command column content of msdb.dbo.sysjobsteps) is executed, a pre-processor checks if there are tokens present and replaces the token with an actual value.

For example: [SQLDIR] will get replaced with C:\MSSQL7 on a default SQL Server 7.0 installation and C:\Program Files\Microsoft SQL Server\MSSQL on a default SQL Server 2000 installation. Note that although this is a string value it is not quoted!

Things you have to know about tokens:

  • Tokens are replaced at runtime, before the job step is executed by the associated sub-system.
  • Tokens are case-sensitive, which is a common cause of problems and mistakes while using them. For example [SQLDIR] will get replaced with the associated token value, but [sqldir] or [SqlDir] not.
  • Tokens are a literal string based replacements, so you will need to add quotes around strings for example or convert the string to the correct data type.
  • Token replacement can NOT be turned off!
  • Tokens can cause naming conflicts after the search and replace, imagine the following TSQL job step that contains a query like this:
    select [APPDIR], [SQLDIR] from [MyDB] where [LOGIN] = N'MSSA'
    This is be altered into (assuming a SQL Server 7.0 installation):
    select [APPDIR], C:\MSSQL7 from [MyDB] where sa = N'MSSA'
  • Debugging token usages is very hard because you can not get the intermediate command text in ant form, so the only usable solutions are "printf" debugging:
    • Use print statements like print N'[SQLDIR' in TSQL or @echo [SQLDIR] in the CmdExec step in combination with an output file on the job step (see Advanced tab on the Job Step properties in SQL Enterprise Manager.
    • Since the Active/X script engine does not provide the ability to log to a file the only option is to use SQLActiveScriptHost object the like this:
      SQLActiveScriptHost.Print "Event from job [JOBID] on [SRVR]"
      This will cause the string specified in the Print method to be written to the message column in the msdb.dbo.sysjobhistory table.

List of tokens:

Token: Description:
[A-DBN] Alert database name
[A-SVR] Alert server name
[A-ERR] Alert error number
[A-SEV] Alert error severity
[A-MSG] Alert message text
[JOBID] Job id as GUID
[STEPID] Step id as tinyint
[DATE] Current date
[TIME] Current time
[MACH] Machine name (computer name)
[MSSA] Master SQLServerAgent name
[STEPCT] Number of times this step has executed (excluding retries)
[SQLDIR] SQLServer root directory, for example [SQLDIR] = C:\MSSQL7 for a default SQL Server 7.0 installation or C:\Program Files\Microsoft SQL Server\MSSQL on a default SQL Server 2000 installation.
[STRTDT] Job start time
[STRTTM] Job start date
[LOGIN] SQL login ID (if connecting non-trusted)
[PSWD] SQL password (if connecting non-trusted)
[OSCMD] Command interpreter prefix (ie. 'command.com /c' or 'cmd.exe /c')
[SRVR] Server name (include instances name if server is an instance)
[INST]  Instance name, returns empty string if default instance

Token sample output:

This table shows example how the literal replaced value looks.

Token: Output:
[A-DBN] empty string (see note 1)
[A-SVR] empty string (see note 1)
[A-ERR] empty string (see note 1)
[A-SEV] empty string (see note 1)
[A-MSG] empty string (see note 1)
[JOBID] 0xBC3DDCF117D7DF4E92BD9BF7B530B2FC (this is a binary representation of a GUID, so convert(uniqueidentifier, [JOBID]) will convert it into a T-SQL unqiueidentifier type
[STEPID] 1
[DATE] 20020215
[TIME] 112535
[MACH] GERTD03
[MSSA] empty string
[STEPCT] 1
[SQLDIR] C:\Program Files\Microsoft SQL Server\MSSQL
[STRTDT] 20020215
[STRTTM] 112535
[LOGIN] MyUID (or [LOGIN] see note 2)
[PSWD] MyPWD (or [PSWD] see note 2)
[OSCMD] cmd.exe /c
[SRVR] GERTD03
[INST] MSSQLSERVER

Download AllTokens.sql, job that dumps content of all tokens to C:\AllTokens.txt

The AllTokens sample job contains one job step like this:

print N'A-DBN  = [A-DBN]'
print N'A-SVR  = [A-SVR]'
print N'A-ERR  = [A-ERR]'
print N'A-SEV  = [A-SEV]'
print N'A-MSG  = [A-MSG]'
print N'JOBID  = [JOBID]'
print N'STEPID = [STEPID]'
print N'DATE   = [DATE]'
print N'TIME   = [TIME]'
print N'MACH   = [MACH]'
print N'MSSA   = [MSSA]'
print N'STEPCT = [STEPCT]'
print N'SQLDIR = [SQLDIR]'
print N'STRTDT = [STRTDT]'
print N'STRTTM = [STRTTM]'
print N'LOGIN  = [LOGIN]'
print N'PSWD   = [PSWD]'
print N'OSCMD  = [OSCMD]'
print N'SRVR   = [SRVR]'
print N'INST   = [INST]'

Which generates output like this:

Job 'AllTokens' : Step 1, 'S1' : Began Executing 2002-02-15 11:25:35

A-DBN  = [SQLSTATE 01000]
A-SVR  = [SQLSTATE 01000]
A-ERR  = [SQLSTATE 01000]
A-SEV  = [SQLSTATE 01000]
A-MSG  = [SQLSTATE 01000]
JOBID  = 0xBC3DDCF117D7DF4E92BD9BF7B530B2FC [SQLSTATE 01000]
STEPID = 1 [SQLSTATE 01000]
DATE   = 20020215 [SQLSTATE 01000]
TIME   = 112535 [SQLSTATE 01000]
MACH   = GERTD03 [SQLSTATE 01000]
MSSA   = [SQLSTATE 01000]
STEPCT = 1 [SQLSTATE 01000]
SQLDIR = C:\Program Files\Microsoft SQL Server\MSSQL [SQLSTATE 01000]
STRTDT = 20020215 [SQLSTATE 01000]
STRTTM = 112535 [SQLSTATE 01000]
LOGIN  = [LOGIN] [SQLSTATE 01000]
PSWD   = [PSWD] [SQLSTATE 01000]
OSCMD  = cmd.exe /c [SQLSTATE 01000]
SRVR   = GERTD03 [SQLSTATE 01000]
INST   = MSSQLSERVER [SQLSTATE 01000]

 

The [SQLSTATE 01000] message are ODBC output indicating an informational message, this can be ignored.

Note 1: Alert based tokens only contain a value when the job is invoked via an alert, otherwise the value is always represented as an empty string.

Note 2: In general when a value is invalid or does not exist an empty string is returned. This is true is all cases but with [LOGIN] and [PSWD], these values are only substituted with a meaningful value if the connection used from SQL Agent to SQL Server is using standard SQL Server security, instead of the default (on Windows NT, Windows 2000 and Windows XP) integrated security. When integrated security is used, the tokens are not replace, so they maintain there current token representation, like they were invalid tokens.

Example using Active/X Script Step (VBScript)

Set SS = CreateObject("SQLDMO.SQLServer")
Set BB = CreateObject("SQLDMO.Backup")
Set DB = CreateObject("SQLDMO.Database")

SS.LoginSecure = True
SS.Connect "[SRVR]"

For Each DB In SS.databases
    If DB.Name <> "tempdb" and DB.Name <> "model" _ And DB.Status <> 992 Then
        BB.Database = DB.Name
        BB.Files = "[SQLDIR]\BACKUP\" + BB.Database + _ "_DB_on_[MACH].[DATE]-[TIME]"
        BB.SQLBackup SS
    End If
Next

SS.Disconnect
set DB=Nothing
set BB=Nothing
set SS=Nothing

Download TokenVBScript.sql

Example using CmdExec Step

Step 1:
DIR [SQLDIR]\LOG

Step 2:
ROBOCOPY [SQLDIR]\LOG C:\BACK *.? /S

Download TokenCmdExec.sql

Example using TSQL step

select * from msdb.dbo.sysjobs where job_id = convert(uniqueidentifier, [JOBID])

Download TokenTSQL.sql

***

Questions or problems regarding this web site should be directed to webmaster@sqldev.net.
Copyright © 1991-2003 SQLDev.Net. All rights reserved.
Last modified: 07/06/03.