Home Dashboard Directory Help
Search

SQL Agent Job - PowerShell step giving escape token error by DTGrayston


Status: 

Closed
 as By Design Help for as By Design


4
0
Sign in
to vote
Type: Bug
ID: 613673
Opened: 10/14/2010 11:19:49 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Hi,

I tbshooted this problem and it appears to me that the sql job engine is incorrectly interpreting the Powershell script command, $(Get-Date) to be the token macro (DATE).

I've got a Job step that is a PowerShell script step and when run it gives this error:

"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."

This is the Powershell script:

Get-ChildItem -path "\\server\share-Logs\" -recurse -include *.log | Where-Object {$_.CreationTime -le $(Get-Date).AddDays(-35)} | Remove-Item


After testing its clear that $(Get-Date) is the cause. But this is a Powershell script command and has nothing to do with tsql and the Escape Token Macro issue that itself is well documented.

Workaround, If the step is re-written like this it runs without error,

$d = Get-Date
Get-ChildItem -path "\\server\share-Logs\" -recurse -include *.log | Where-Object {($_.CreationTime -le $d.AddDays(-35))} | Remove-Item

So "$(Get-Date)" gives the Escape Macro error whereas "$d = Get-Date" does not. Of course this all is a Powershell script commands and not at all tsql where the (DATE) macro would apply.
Details
Sign in to post a comment.
Posted by Microsoft on 11/1/2010 at 12:07 PM
Hello David,

We will go ahead and close this issue.

Thanks
Alex Grach
Posted by Microsoft on 10/20/2010 at 3:12 PM
Hello,

I need to make a slight correction. The problem happens because of the syntax similarities (not in the similarity of the tokens and verbs). So if you are using powershell with the $( syntax, the workaround is required.

Thanks
Robann Mateja
Microsoft Sql Server PM
Posted by DTGrayston on 10/20/2010 at 3:09 PM
Thank you for confirming this issue and I'll continue to use the workaround.
Posted by Microsoft on 10/20/2010 at 2:57 PM
Thank you for the feedback. We have verified that this is an issue because the similarities between some Powershell verbs and Sql Agent tokens. Please use the workaround desrcibed (re-write the steps to assign the Powershell verb to a variable, and use the variable where the in line Powershell function would be used). We will be updating the Sql Agent Token kb articles to describe this issue and workaround.

Thank you
Robann Mateja
Microsoft Sql Server PM
Posted by Microsoft on 10/18/2010 at 2:17 PM
Thank you for reporting this issue - we are investigating and we will get back to you shortly.

Thanks,

Alex Grach
Sign in to post a workaround.
Posted by DTGrayston on 10/14/2010 at 11:20 AM
If the step is re-written like this it runs without error,

$d = Get-Date
Get-ChildItem -path "\\server\share-Logs\" -recurse -include *.log | Where-Object {($_.CreationTime -le $d.AddDays(-35))} | Remove-Item