October 24, 2013 at 2:22 pm
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.
October 25, 2013 at 2:58 am
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!!!
October 25, 2013 at 7:08 am
Yes the script runs successfully on the sql server when run from windows explorer: right click>Run with Powershell and from CMD.
October 25, 2013 at 7:24 am
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!!!
October 25, 2013 at 7:26 am
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.
October 25, 2013 at 7:29 am
The special character didn't translate in my last post. It was black and diamond shaped with a white question mark inside it.
October 25, 2013 at 7:36 am
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!!!
October 25, 2013 at 7:54 am
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.
October 25, 2013 at 9:01 am
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