Consider a situation where in one needs to have SQL Jobs to be more independent of machine/instance and/or the main job itself. In such cases we can utilize one of the features of SQL Server called "TOKENS".
When a Job step is written using tokens it gives the same flexibility that "Variables" provide in software programs. I hope that makes sense! Many of us do understand the meaning and usage of Variables... so will not explain the same.
When a token is used in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.
Note: I have tried to explain the usage of tokens by making use of the following example.
For example: Consider a situation wherein we are saving the output logs of a Job from multiple servers (Development, Testing, and Production) to one central location. In such cases, we have two options of specifying the output path.
1. Manually setting the path for job on each of the Servers.
2. Or making use of "TOKENS" which makes our life easier.
Let’s make use of the tokens.
This centralization of output log location allows the use of common scripts for all environments.
Servername can be replaced by using SQL Server Agent Token [MACH], [DATE] and configure output to a common location shown below.
\abcd\Prod\SQLServersJobs\LOGS\
The server name differentiates the environment context of the log.
Note: The token templates used are different for SQL versions.
<Template>
SQL2000
[MACH]\[INST]_<jobname>_Step1_[DATE].log -- Where <jobname> is the name of the job.
Example: SQL2000
\abcd\Prod\SQLServersJobs\LOGS\[MACH]_[INST]\MyJob_Step1_[DATE].log
The above would be converted to: (see that default instance returns as MSSQLSERVER)
\abcd\Prod\SQLServersJobs\LOGS\A2MDEV101_MSSQLSERVER\MyJob_Step1_20091005.log
SQL2005
$(ESCAPE_NONE(MACH))\$(ESCAPE_NONE(INST))_<jobname>_Step1_$(ESCAPE_NONE(DATE)).log --Where <jobname> is the name of the job.
Example: SQL2005 (SP1 & higher) & SQL2008
\abcd\Prod\SQLServersJobs\LOGS\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\MyJob_Step1_$(ESCAPE_NONE(DATE)).log
The above would be converted to: (see that default instance returns as MSSQLSERVER)
\abcd\Prod\SQLServersJobs\LOGS\A2MPRD151_LOGGING\MyJob_Step1_20091005.log
And that is all for now.
For more on tokens one can refer to the following web link:
http://msdn.microsoft.com/en-us/library/ms175575.aspx
When a Job step is written using tokens it gives the same flexibility that "Variables" provide in software programs. I hope that makes sense! Many of us do understand the meaning and usage of Variables... so will not explain the same.
When a token is used in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.
Note: I have tried to explain the usage of tokens by making use of the following example.
For example: Consider a situation wherein we are saving the output logs of a Job from multiple servers (Development, Testing, and Production) to one central location. In such cases, we have two options of specifying the output path.
1. Manually setting the path for job on each of the Servers.
2. Or making use of "TOKENS" which makes our life easier.
Let’s make use of the tokens.
This centralization of output log location allows the use of common scripts for all environments.
Servername can be replaced by using SQL Server Agent Token [MACH], [DATE] and configure output to a common location shown below.
\abcd\Prod\SQLServersJobs\LOGS\
The server name differentiates the environment context of the log.
Note: The token templates used are different for SQL versions.
<Template>
SQL2000
[MACH]\[INST]_<jobname>_Step1_[DATE].log -- Where <jobname> is the name of the job.
Example: SQL2000
\abcd\Prod\SQLServersJobs\LOGS\[MACH]_[INST]\MyJob_Step1_[DATE].log
The above would be converted to: (see that default instance returns as MSSQLSERVER)
\abcd\Prod\SQLServersJobs\LOGS\A2MDEV101_MSSQLSERVER\MyJob_Step1_20091005.log
SQL2005
$(ESCAPE_NONE(MACH))\$(ESCAPE_NONE(INST))_<jobname>_Step1_$(ESCAPE_NONE(DATE)).log --Where <jobname> is the name of the job.
Example: SQL2005 (SP1 & higher) & SQL2008
\abcd\Prod\SQLServersJobs\LOGS\$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(INST))\MyJob_Step1_$(ESCAPE_NONE(DATE)).log
The above would be converted to: (see that default instance returns as MSSQLSERVER)
\abcd\Prod\SQLServersJobs\LOGS\A2MPRD151_LOGGING\MyJob_Step1_20091005.log
And that is all for now.
For more on tokens one can refer to the following web link:
http://msdn.microsoft.com/en-us/library/ms175575.aspx
No comments:
Post a Comment