{"id":155,"date":"2016-07-28T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/sql-agent-2016-jobs-running-powershell-scripts"},"modified":"2026-03-18T21:52:35","modified_gmt":"2026-03-18T21:52:35","slug":"sql-agent-2016-jobs-running-powershell-scripts","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2016\/07\/28\/sql-agent-2016-jobs-running-powershell-scripts\/","title":{"rendered":"SQL Agent 2016 Jobs Running PowerShell Scripts"},"content":{"rendered":"<p>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.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/running-powershell-in-a-sql-agent-job\/\">This post<\/a> 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.<\/p>\n<p>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 <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/2865564\/sqlps-exes-sqlserver-provider-causes-invalid-path-errors-with-filesystem-cmdlets\">Microsoft Connect ticket<\/a> so we can get the issue I am about to explain fixed.<\/p>\n<h2>The problem<\/h2>\n<p>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\u00a0provider under the context of the SQLSERVER provider the cmdlet will fail.<\/p>\n<h3>SQL Agent reproduction steps<\/h3>\n<ul>\n<li>Create a SQL Agent job which attempts a Get-ChildItem call to a UNC path with the PowerShell step type.<\/li>\n<\/ul>\n<p>This is my script: \u2018Get-ChildItem \\\\git\\SourceControl-DEV\\Test\u2019<\/p>\n<ul>\n<li>Run the job.<\/li>\n<\/ul>\n<p>The job will fail with the below error.<\/p>\n<p>> 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 \u2018Get-ChildItem \\\\git\\SourceControl-DEV\\Test\u2019. Correct the script and reschedule the job. The error information returned by PowerShell is: \u2018Cannot find path \u2018\\\\git\\SourceControl-DEV\\Test\u2019 because it does not exist. \u2018. Process Exit Code 0. The step succeeded.<\/p>\n<h3>Console reproduction steps<\/h3>\n<ul>\n<li>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.<\/li>\n<\/ul>\n<ul>\n<li>View a UNC path: \u2018Get-ChildItem \\\\git\\SourceControl-DEV\\Test\u2019<\/li>\n<\/ul>\n<p>Step 2 will succeed.<\/p>\n<ul>\n<li>Change working directory by running, \u2018Set-Location SQLSERVER:\\\u2019<\/li>\n<\/ul>\n<ul>\n<li>Re-execute \u2018Get-ChildItem \\\\git\\SourceControl-DEV\\Test\u2019<\/li>\n<\/ul>\n<p>The script\u00a0will fail with the below error:<\/p>\n<p>> Get-ChildItem : Cannot find path \u2018\\\\git\\SourceControl-PROD\\Test\u2019 because it does not exist.<\/p>\n<p>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.<\/p>\n<h2>Work around<\/h2>\n<p>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.<\/p>\n<pre><code class=\"language-\">Set-Location C:\\;\nGet-ChildItem \\\\git\\sourcecontrol-prod\\test<\/code><\/pre>\n<h2>Long-term fix<\/h2>\n<p>Vote up the <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/2865564\/sqlps-exes-sqlserver-provider-causes-invalid-path-errors-with-filesystem-cmdlets\">Microsoft Connect ticket<\/a> and then install whichever update that the fix is released in.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,6,7,10,11,13,14,15,16],"tags":[],"class_list":["post-155","post","type-post","status-publish","format-standard","hentry","category-administration","category-career","category-community","category-disaster-recovery","category-general","category-microsoft-sql-server","category-performance","category-powershell","category-sql-agent"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=155"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/155\/revisions"}],"predecessor-version":[{"id":417,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/155\/revisions\/417"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}