Using Token in SQL Agent Job

[Shailesh Khanal]

I have created an alert for database mirroring, the alert fires when the
mirror is behind principal by 15 minutes, I am trying to setup a response
job which will log the alert with database name and server name, etc. But
even simple print statement in the job is failing when I use a token like

PRINT N'Current database name is $(ESCAPE_SQUOTE(A-MSG))' ;

The error is

Unable to start execution of step 1 (reason: Variable A-MSG not found). The
step failed.

Am I missing something?

Thank you

[Tibor Karaszi]

I tried and failed as well. Until I read below section in Books Online. After doing hat below
instructs, the alert token worked just fine.

"Any Windows user with write permissions on the Windows Event Log can access job steps that are
activated by SQL Server Agent alerts or WMI alerts. To avoid this security risk, SQL Server Agent
tokens that can be used in jobs activated by alerts are disabled by default. These tokens are:
A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property).

If you need to use these tokens, first ensure that only members of trusted Windows security groups,
such as the Administrators group, have write permissions on the Event Log of the computer where SQL
Server resides. Then, right-click SQL Server Agent in Object Explorer, select Properties, and on the
Alert System page, select Replace tokens for all job responses to alerts to enable these tokens."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

I have created an alert for database mirroring, the alert fires when the mirror is behind principal
by 15 minutes, I am trying to setup a response job which will log the alert with database name and
server name, etc. But even simple print statement in the job is failing when I use a token like
PRINT N'Current database name is $(ESCAPE_SQUOTE(A-MSG))' ;
The error is
Unable to start execution of step 1 (reason: Variable A-MSG not found). The step failed.
Am I missing something?
Thank you

[Shailesh Khanal]

Thanks Tibor.

I read the same topic but simply missed the "Replace tokens for all job
responses to alerts to enable these tokens" part.

It is working now.

Post by Tibor Karaszi
I tried and failed as well. Until I read below section in Books Online.
After doing hat below instructs, the alert token worked just fine.
"Any Windows user with write permissions on the Windows Event Log can
access job steps that are activated by SQL Server Agent alerts or WMI
alerts. To avoid this security risk, SQL Server Agent tokens that can be
used in jobs activated by alerts are disabled by default. These tokens
are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property).
If you need to use these tokens, first ensure that only members of trusted
Windows security groups, such as the Administrators group, have write
permissions on the Event Log of the computer where SQL Server resides.
Then, right-click SQL Server Agent in Object Explorer, select Properties,
and on the Alert System page, select Replace tokens for all job responses
to alerts to enable these tokens."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Shailesh Khanal
I have created an alert for database mirroring, the alert fires when the
mirror is behind principal by 15 minutes, I am trying to setup a response
job which will log the alert with database name and server name, etc. But
even simple print statement in the job is failing when I use a token like
PRINT N'Current database name is $(ESCAPE_SQUOTE(A-MSG))' ;
The error is
Unable to start execution of step 1 (reason: Variable A-MSG not found).
The step failed.
Am I missing something?
Thank you

[Shailesh Khanal]

I am trying to get the database name that is causing the alert because of
delay in mirroring, but the A-DBN token returns me MSDB, that's not helpful.

Post by Shailesh Khanal
Thanks Tibor.
I read the same topic but simply missed the "Replace tokens for all job
responses to alerts to enable these tokens" part.
It is working now.

Post by Tibor Karaszi
I tried and failed as well. Until I read below section in Books Online.
After doing hat below instructs, the alert token worked just fine.
"Any Windows user with write permissions on the Windows Event Log can
access job steps that are activated by SQL Server Agent alerts or WMI
alerts. To avoid this security risk, SQL Server Agent tokens that can be
used in jobs activated by alerts are disabled by default. These tokens
are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property).
If you need to use these tokens, first ensure that only members of
trusted Windows security groups, such as the Administrators group, have
write permissions on the Event Log of the computer where SQL Server
resides. Then, right-click SQL Server Agent in Object Explorer, select
Properties, and on the Alert System page, select Replace tokens for all
job responses to alerts to enable these tokens."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Shailesh Khanal
I have created an alert for database mirroring, the alert fires when the
mirror is behind principal by 15 minutes, I am trying to setup a response
job which will log the alert with database name and server name, etc. But
even simple print statement in the job is failing when I use a token like
PRINT N'Current database name is $(ESCAPE_SQUOTE(A-MSG))' ;
The error is
Unable to start execution of step 1 (reason: Variable A-MSG not found).
The step failed.
Am I missing something?
Thank you

[Tibor Karaszi]

I guess that would be because the source for the error in question is generated from the msdb
database. Is there some other token that you can use? If not, consider posting a request at
http://connect.microsoft.com for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

I am trying to get the database name that is causing the alert because of delay in mirroring, but
the A-DBN token returns me MSDB, that's not helpful.

Post by Shailesh Khanal
Thanks Tibor.
I read the same topic but simply missed the "Replace tokens for all job responses to alerts to
enable these tokens" part.
It is working now.

Post by Tibor Karaszi
I tried and failed as well. Until I read below section in Books Online. After doing hat below
instructs, the alert token worked just fine.
"Any Windows user with write permissions on the Windows Event Log can access job steps that are
activated by SQL Server Agent alerts or WMI alerts. To avoid this security risk, SQL Server
Agent tokens that can be used in jobs activated by alerts are disabled by default. These tokens
are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property).
If you need to use these tokens, first ensure that only members of trusted Windows security
groups, such as the Administrators group, have write permissions on the Event Log of the
computer where SQL Server resides. Then, right-click SQL Server Agent in Object Explorer, select
Properties, and on the Alert System page, select Replace tokens for all job responses to alerts
to enable these tokens."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

I have created an alert for database mirroring, the alert fires when the mirror is behind
principal by 15 minutes, I am trying to setup a response job which will log the alert with
database name and server name, etc. But even simple print statement in the job is failing when I
use a token like
PRINT N'Current database name is $(ESCAPE_SQUOTE(A-MSG))' ;
The error is
Unable to start execution of step 1 (reason: Variable A-MSG not found). The step failed.
Am I missing something?
Thank you