question

RuoRuo-5318 avatar image
0 Votes"
RuoRuo-5318 asked TomPhillips-1744 edited

How to Run Powershell Command in SQL Sever Agent Job

I have a variable in the powershell command. We are considering to run it in SQL Agent job step 1. However I always get the below error.

Command:
$a = $(Get-Date).AddMonths(1).ToString("yyyyMM")
Write-Output "output____$a"

Error message:
Unable to start execution of step 1 (reason: The job step contains tokens. For SQL Server 2005 Service Pack 1 or later, you must use the appropriate ESCAPE_xxx macro to update job steps containing tokens before the job can run.). The step failed.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RuoRuo-5318 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

The SQL Agent job thinks "$(Get-Date)" is an unrecognized token. $() is reserved for tokens in SQL Agent.

The simplest answer is to do:

 $d = Get-Date
 $a = $d.AddMonths(1).ToString("yyyyMM")
 Write-Output "output____$a"
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Put the commands in a script file rather than directly into the job text?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered RuoRuo-5318 commented

Hi @RuoRuo-5318,

Welcome to Microsoft Q&A!
Starting with SQL Server 2005 Service Pack 1, the SQL Server Agent job step token syntax changed. As a result, an escape macro must now accompany all tokens used in job steps, or else those job steps will fail. Using escape macros and updating your SQL Server Agent job steps that use tokens are described in the following sections, "Understanding Using Tokens," "SQL Server Agent Tokens and Macros," and "Updating Job Steps to Use Macros." In addition, the SQL Server 2000 syntax, which used square brackets to call out SQL Server Agent job step tokens (for example, "[DATE]") has also changed. You must now enclose token names in parentheses and place a dollar sign ($) at the beginning of the token syntax. For example:
$(ESCAPE_ macro name (DATE))
Please refer to this document: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175575(v%3dsql.110)

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

what macro name should I use?

0 Votes 0 ·

Hi @RuoRuo-5318 ,

Did you try $d = Get-Date first?

Best regards,
Seeya

0 Votes 0 ·

Do you try it? Same error message.

0 Votes 0 ·