Can't get powershell script to run as Agent Job - runs from CMD

  • I thought I'd try Powershell for some file maintenance. Thought it might be fun! And now I'm stuck.

    Can anyone see what I'm doing wrong?

    I'm having trouble with Rename-Item. This line works from CMD:

    Dir \\MyServer\MyShare\Transfer\*.txt | Rename-Item –Newname{($_.basename + (get-date -format '_MMddyyyy') + $_.extension)}

    But fails when run as part of a script in an Agent Job Step.

    Here's the entire script:

    #add the date - files will be named filename_mmddyyyy.txt

    Set-Location \\MyServer\MyShare

    Dir \\MyServer\MyShare\Transfer\*.txt | Rename-Item –Newname{($_.basename + (get-date -format '_MMddyyyy') + $_.extension)}

    # copy the .txt files from MyShare\Tranfer to MyShare

    Copy-Item -Path \\MyServer\MyShare\transfer\*.txt -Destination \\MyServer\MyShare

    # delete the .txt files from the Transfer folder

    Remove-Item \\MyServer\MyShare\transfer\*.txt

    Here's the error message:

    Executed as user: MyHouse\sqladmin. A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Dir \\MyServer\MyShare\Transfer\*.txt | Rename-Item ?Newname {($_.basename + (get-date -format '_MMddyyyy') + $_.extension)}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A positional parameter cannot be found that accepts argument '($_.basename + (get-date -format '_MMddyyyy') + $_.extension)'. '. Process Exit Code -1. The step failed.

  • Have you run the whole script successfully outside of an agent job? My thinking is confirming whether it is an issue with the script or the script being run in the context of an agent job.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Yes the script runs successfully on the sql server when run from windows explorer: right click>Run with Powershell and from CMD.

  • Does the credentials that the SQL Server Agent job run under have permission to access the UNC location? You can test this by adding the DIR part of line 3 as its own line before line 3 to see if it moves the error to line 4.

    Also, you could output the values by using a variable to hold the value of ($_.basename + (get-date -format '_MMddyyyy') + $_.extension) and logging it before using it as a parameter to the Rename-Item call.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Found the problem and fixed, but I don't know hows or whys. When I added the code to the Job Step by selecting Open and browsing to the .ps1 I got this funny question mark thing in the code.

    ...Rename-Item ?Newname{($_.basename + (get-date -format

    Changed that to -Newname and it works as expected.

  • The special character didn't translate in my last post. It was black and diamond shaped with a white question mark inside it.

  • I guess this may be due to something like codepages which I understand in theory but try to steer clear of in practice!!!

    Glad you have resolved it.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • I opened the script in Notepad++ and said show all characters and there's no special character there. Only when I click Open in the Agent Job Step and browse to the .ps1 does it appear. If I copy/paste the code from Notepad++ it does not appear. Curious.

  • I should imagine that the Copy/Paste operation uses plain text (i.e. the job editor only handles plain text from the clipboard), however, opening the file would require it to handle UTF-8.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 9 posts - 1 through 8 (of 8 total)

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