Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Randy Doub
Randy Doub
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 756
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.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8223 Visits: 6114
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!!!
Randy Doub
Randy Doub
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 756
Yes the script runs successfully on the sql server when run from windows explorer: right click>Run with Powershell and from CMD.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8223 Visits: 6114
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!!!
Randy Doub
Randy Doub
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 756
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.
Randy Doub
Randy Doub
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 756
The special character didn't translate in my last post. It was black and diamond shaped with a white question mark inside it.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8223 Visits: 6114
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!!!
Randy Doub
Randy Doub
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 756
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.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8223 Visits: 6114
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!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search