SQL Agent 2016 Jobs Running PowerShell Scripts

I recently performed a side-by-side upgrade of a SQL Server 2008 R2 instance to SQL Server 2016 RTM. This particular instance had over 200 SQL Agent jobs running various PowerShell scripts. After the upgrade I had to modify nearly all of those PowerShell scripts because of a bug with the PowerShell SQLSERVER provider that is packaged with SQL Server 2016.

This post maps out the manner which PowerShell is executed throughout the various versions. There are two types of job steps that are used to execute PowerShell scripts. There is a PowerShell step which calls the SQLPS.exe mini-shell and then you can use the CmdExec type which calls cmd.exe where you can call powershell.exe yourself.

If I had been using the CmdExec step type, I would not have had to change anything but I was using the PowerShell step type and the SQLPS.exe mini-shell was shipped with a bug in SQL Server 2016 RTM. I recommend you vote up my Microsoft Connect ticket so we can get the issue I am about to explain fixed.

The problem

When executed through the SQL Agent, the SQLPS.exe mini-shell is called and the current working directory is switched to the SQLSERVER:\ provider. When you call a cmdlet that uses the FILESYSTEM provider under the context of the SQLSERVER provider the cmdlet will fail.

SQL Agent reproduction steps

  1. Create a SQL Agent job which attempts a Get-ChildItem call to a UNC path with the PowerShell step type.
    1. This is my script: ‘Get-ChildItem \\git\SourceControl-DEV\Test’
  2. Run the job.

The job will fail with the below error.

Executed as user: DOMAIN\serviceAcct. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘Get-ChildItem \\git\SourceControl-DEV\Test’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find path ‘\\git\SourceControl-DEV\Test’ because it does not exist. ‘. Process Exit Code 0. The step succeeded.

Console reproduction steps

  1. On a fresh install of Windows Server 2012 R2 with a fresh install of SQL Server 2016 RTM, run C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\SQLPS.exe.
  2. View a UNC path: ‘Get-ChildItem \\git\SourceControl-DEV\Test’
    1. Step 2 will succeed.
  3. Change working directory by running, ‘Set-Location SQLSERVER:\’
  4. Re-execute ‘Get-ChildItem \\git\SourceControl-DEV\Test’

The script will fail with the below error:

Get-ChildItem : Cannot find path ‘\\git\SourceControl-PROD\Test’ because it does not exist.

It is important to note that, unlike the SQL Agent steps, calling the SQLPS.exe mini-shell directory does not change your working directory to the SQLSERVER provider but the underlying problem still exists.

Work around

The solution to this problem is to change the working directory back to the FILESYSTEM provider before running any commands which use the FILESYSTEM provider. With our example scenario the work around script will look like this.

Set-Location C:\;
Get-ChildItem \\git\sourcecontrol-prod\test

Long-term fix

Vote up the Microsoft Connect ticket and then install whichever update that the fix is released in.

This article has 2 comments

  1. […] Derik Hammer notes that there is a bug in 2016 with SQL Agent jobs which have Powershell step types: […]

  2. […] are a few things you should know.  There is a post by SQLHammer that illustrates one of them here. The one that shows that if you are using a PowerShell job step, you will want to use a […]

Leave a Reply