sql-server-agent,powershell – SQL Job PowerShell Task Fails but Succeeds as User

powershellsql-server-agent

Simple requirement: delete files on remote path older than 20 days.

PS C:\Users\Administrator> Get-ChildItem `
    -Path \\FileServer\SQLBackups\SQLServer\ `
    -File -Recurse -Force | 
  Where-Object {
    ($_.LastWriteTime -lt (Get-Date).AddDays(-20))
} | Remove-Item

The above command succeeds (vanity backticks & linebreaks added for readability).

The relevant SQL 2016 Job (backticks and linebreaks not added, but the command text is the same)

@subsystem=N'PowerShell',
@command=N'Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item', 
@proxy_name=N'XPProxy'

The error information returned by PowerShell is:

'A parameter cannot be found that matches parameter name 'File'.

No permissions issues (refer proxy_name);
SQL Powershell version:

Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      -1     -1

SQL @@VERSION:

Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Below is the actual parameter list of the Get-ChildItem function. Why can't SQL use the -File parameter?

Get-ChildItem 
   [[-Path] <String[]>] 
   [[-Filter] <String>] 
   [-Attributes {ReadOnly | Hidden | System | Directory | Archive | Device | Normal | Temporary | SparseFile | ReparsePoint | Compressed | Offline | NotContentIndexed | Encrypted | IntegrityStream | NoScrubData}] 
   [-Depth <UInt32>] 
   [-Directory] 
   [-Exclude <String[]>] 
   [-File] 
   [-Force] 
   [-Hidden] 
   [-Include <String[]>] 
   [-Name] 
   [-ReadOnly] 
   [-Recurse] 
   [-System] 
   [-UseTransaction] 
   [<CommonParameters>]

Best Answer

cd C:

Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item

https://www.sqlservercentral.com/forums/topic/powershell-for-network-path http://www.midnightdba.com/Jen/2013/05/quick-tip-navigating-to-a-unc-within-sqlps/

Adding the cd C: line to my Powershell task in SQL Job allowed UNC references to resolve remote server file directory.

To quote a wise man: "SQL Server Agent integrates with powershell in very unintuitive ways sometimes"

Thanks for your help, Peter.

Related Question