How to Run Powershell Command in SQL Sever Agent Job

RuoRuo 41 Reputation points
2021-10-10T00:06:19.96+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,756 questions
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2021-10-15T12:15:31.737+00:00

    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"
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-10-10T08:59:32.587+00:00

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

    1 person found this answer helpful.
    0 comments
  2. Seeya Xi-MSFT 16,441 Reputation points
    2021-10-11T03:06:25.583+00:00

    Hi @RuoRuo ,

    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://learn.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.