Post reply

SQL Server Agent running powershell commands

  • 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

    }

    }

  • 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

  • January 20, 2012 at 1:49 pm

    #1436736

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

    thanks

  • February 1, 2012 at 5:23 pm

    #1441498

    What was the actual solution?

  • 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

  • July 20, 2014 at 6:51 am

    #1730862

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply