Error running Powershell Script in SQL Server Agent

Question

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name of the folder that the application is in (the folder is the version number), so I'm using PowerShell to find it:

set-location "\\server\companydocuments\MyApp\Application Files\"
$name = Get-ChildItem | sort name -desc | select -f 1 | select name
cd $name.name
& ".\Application.exe"

If I open a PowerShell window on the SQL Server then it works fine. When I run this in SQL Server Agent I get the following error:

A job step received an error at line 1 in a PowerShell script.
The corresponding line is 'set-location "\\server\companydocuments\MyApp\Application Files\'"

I thought it might be a permissions issue so I've tried SQL Server Agent running under my credentials (it's a test server, not production) but I still get the same error. I've also tried mapping a network drive to the shared folder instead of UNC paths, but the same error.

Can anyone suggest how I can connect to this folder?

Answer 1

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use.

Your code would look something like below:

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\server\companydocuments\MyApp\Application Files\"

You could also wrap the remainder into two lines if you wanted to:

cd (Get-ChildItem | Sort-Object name -Descending | Select name -First 1).name
& ".\Application.exe"

Answer 2

The other alternative would be to use operating system command and give powershell.exe c:/path/script.ps1 as command and save your script in script.ps1. This will ensure using OS powershell instead of using provider.