Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

Writing Powershell Steps Caveat

Writing Powershell Steps Caveat

  • Comments 2

Sql Agent provides tokens to be used with T-Sql job step scripts. Tokens are names (strings) that are replaced at runtime with a value. The tokens provide a feature similar to variables in code. The tokens are described in MSDN (http://msdn.microsoft.com/en-us/library/ms175575.aspx).

In 2005 and 2008, the token syntax has changed and the tokens must now be escaped to avoid sql injection. The escape syntax is enforced on all subsystems.

The syntax of tokens collides with the syntax for Powershell scripting. When using ($Scriptlet) call in a powershell script, the Sql Agent engine tries to parse this as a token and it will throw the following 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."

You can see this error by checking the job history.

The solution is to separate the scriptlet call into a separate variable.

Example :

 

This is the Powershell script that causes the problem:


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

$(Get-Date) is the cause.

Rewrite the script:

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

And it works!

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • Hi,

    The Agent Powershell feature has a lot of promise.  It would be great to see additional tips and caveats on running Powershell from and Agent step.  Such as  Write-Host does not work (no console), use Write-Output instead.  Here are some topics I would like to see.

    * How to trap errors.  Often all you see is "Syntax error at line 25".  Other users suggest using Try-Catch block, or writing to the Event Log and iterating through the .InnerExceptions.  Some examples would be great.

    * Update to the Step UI.  Such as a Powershell syntax validation button.

    Thanks!

    Rob (robm26@yahoo.com)

  • Thanks a lot .. exaclty wht I needed !!!

Page 1 of 1 (2 items)