question

paws27284 avatar image
paws27284 asked

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
powershellsql job
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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