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

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.