Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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"
share|improve this answer
    
Thanks, worked perfectly –  Greg Oct 21 '13 at 23:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.