Powershell script works in Powershell but not as a SQL job Step

Question

question

paws27284 asked Aug 12, '20

Powershell script works in Powershell but not as a SQL job Step

This PS script works in the PS window on my SQL server 2012, but does not work as a SQL Job step. The SQL job step errors with "error information returned by PowerShell is: 'Cannot call method. The provider does not support the use of filters. '. Process Exit Code -1. The step failed." Is it possible for the version of PS and the version pf PS used by SQL to be different? Suggestions for a fix? Basically need to copy the latest version of a file to a different location with different name.

$filterNTC="*_filename.csv"
$dir = "\\networkloc\folder\folder\folder\folder\folder\"
$latestNTC = Get-ChildItem -Path $dir -Filter $filterNTC | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$sourcefile = "\\networkloc\folder\folder\folder\folder\folder\" + $latestNTC.name
Copy-Item -Path $sourcefile -Destination D:\folder\folder\folder\folder\filename.csv

powershell sql job

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-08-12T18:23:07Z

Magnus Ahlkvist answered Aug 12, '20

I'm gonna guess it's a Powershell jobstep you're configuring, not a SQL job step.

Anyway, if you want to execute Powershell, you don't want to run a Powershell jobstep, because the Powershell jobstep will execute using SQLPS.exe which is not the same as Powershell.exe (or pwsh.exe for Powershell core).

To execute using powershell.exe or pwsh.exe, you want to create a CmdExec jobstep, and run the command

powershell.exe -File <path to your .ps1 file containing your script

Or pwsh.exe with the same parameters for Powershell core.

Good luck!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question