SQL Server Agent running powershell commands

• forco02

  Old Hand

  Points: 358

  More actions

  February 8, 2011 at 3:56 am

  #234588

  Hi All,

  I am trying to run the following powershell script as a job from SQL server Agent (2008 R2), however, I

  get:

  "Unable to start execution of step 2 (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."

  I run other jobs as Type "powershell" defined steps with variables which works fine, but I cannot get my head around which bit is the "Token". All help appreciated.

  Thanks,

  Colin.

  $source_dir = "B:\Database_Backups"

  $target_dir = "\\machine-b\backup_Database_Backups"

  foreach ($i in Get-ChildItem $source_dir)

  {

  if ($i.CreationTime -gt ($(Get-Date).AddDays(-2)))

  {

  Copy-Item $i.FullName $target_dir

  }

  }

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  February 8, 2011 at 4:53 am

  #1283601

  I think the $ sign is misleading SQL Server Agent, as he thinks it is a job token, while in fact it is a powershell variable.

  http://msdn.microsoft.com/en-us/library/ms175575.aspx

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• SQlLogic

  SSCrazy Eights

  Points: 9686

  More actions

  January 20, 2012 at 1:49 pm

  #1436736

  That was very helpful link...solved my problem and my job is running smoothly now .

  thanks

• larry Hennig

  SSChasing Mays

  Points: 656

  More actions

  February 1, 2012 at 5:23 pm

  #1441498

  What was the actual solution?

• forco02

  Old Hand

  Points: 358

  More actions

  February 2, 2012 at 12:43 am

  #1441574

  Put the date to a variable first it is the Get-Date that seems to be the problem in my example above.

  $source_dir = "B:\Database_Backups"

  $target_dir = "\\machine-b\Database_Backups"

  $d = Get-Date

  Get-ChildItem $source_dir\*.* -include *.bak, *.trn | where {$_.CreationTime -gt ($d.AddDays(-1))} | Copy-Item -Destination $target_dir

• tuyendq

  Grasshopper

  Points: 23

  More actions

  July 20, 2014 at 6:51 am

  #1730862

  Thank you! It works for me to ($d = Get-Date).