2

I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing.

The problem is the script works fine when I manually execute. It also executes fine when I execute it as SQL Server Agent service account. But the same script fails when I try to execute as part of SQL Server Agent job. Am I missing something here?

$path = "\\server\shared\path\01222020"
New-Item -path $path -ItemType Directory -Force

Exception:
Date        1/22/2020 5:25:10 PM
Log     Job History (test)

Step ID     1
Server      serverA
Job Name        Test
Step Name       Test
Duration        00:00:01
Sql Severity    0
Sql Message ID  0
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   0

Message
Executed as user: domain\agent_service_account. A job step received an error at 
line 9 in a PowerShell script. The corresponding line is 'New-Item -path $path 
-ItemType Directory '. Correct the script and reschedule the job. The error 
information returned by PowerShell is: 'Invalid Path: 
'\\server\shared\path\01222020'.  '.  Process Exit Code -1.  The step failed.
  • 3
    Not enough for an answer, but check out dbatools.io/agent for guidance on how best to execute PowerShell in Agent jobs – alroc Jan 23 at 20:42
7

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.

Alternatively, you can change directory beforehand to a local drive (cd C:) and it should then work without prefixing the provider name, but you may want to stay in the default SQLSERVER:\SQL\SERVERNAME\INSTANCENAME path depending on what your script requires.

More info:

https://dbatools.io/agent/

https://social.technet.microsoft.com/Forums/windowsserver/en-US/ec7f3ae8-d196-459e-b9dc-e6ed0df93004/running-powershell-from-sql-server-using-unc-paths

|improve this answer|||||
  • perfect, both solutions worked for me & that you for the reference links. – sqlcheckpoint Jan 23 at 23:12
-2

sqlcheckpoint,

Please take a look at the link below, which describes which Powershell modules are used by SQL Agent. Its either SQLPS or SqlServer depending upon your version of SQL Server.

https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

It quite likely the New-Item cmdlet is not found in the powershell module used by SQLAgent.

You could create a SQLAgent job with a CmdExec step, which in turn calls powershell.exe.

powershell.exe -ExecutionPolicy ByPass -NoProfile -File PATH_TO_YOUR_SCRIPT

See the link below for all the options/parameters for powershell.exe

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_powershell_exe?view=powershell-5.1

|improve this answer|||||
  • Agree that this is probably on the right track. In general, I now just create a .bat file to run PowerShell scripts, and then have the Agent step execute the .bat file. – Tony Hinkle Jan 23 at 20:18
  • 2
    New-Item being not found as a cmdlet would return a much different error message. Try it yourself by attempting to execute New-Foo – Peter Vandivier Jan 23 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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