Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't get powershell script to run as Agent Job - runs from CMD Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:57 PM
Points: 147, Visits: 645
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.
Post #1508266
Posted Friday, October 25, 2013 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:37 PM
Points: 5,207, Visits: 2,848
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!!!
Post #1508368
Posted Friday, October 25, 2013 7:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:57 PM
Points: 147, Visits: 645
Yes the script runs successfully on the sql server when run from windows explorer: right click>Run with Powershell and from CMD.
Post #1508447
Posted Friday, October 25, 2013 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:37 PM
Points: 5,207, Visits: 2,848
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!!!
Post #1508453
Posted Friday, October 25, 2013 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:57 PM
Points: 147, Visits: 645
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.
Post #1508454
Posted Friday, October 25, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:57 PM
Points: 147, Visits: 645
The special character didn't translate in my last post. It was black and diamond shaped with a white question mark inside it.
Post #1508456
Posted Friday, October 25, 2013 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:37 PM
Points: 5,207, Visits: 2,848
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!!!
Post #1508460
Posted Friday, October 25, 2013 7:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:57 PM
Points: 147, Visits: 645
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.
Post #1508466
Posted Friday, October 25, 2013 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:37 PM
Points: 5,207, Visits: 2,848
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!!!
Post #1508496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse