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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 613673 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/14/2010 11:19:49 AM
Access Restriction Public

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