6

We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is now failing.

The job has a Powershell job step:

$ErrorActionPreference = 'Stop'
$DateStamp = Get-Date -format "yyyyMMdd"

$BizUnit = 'SHB'

# Target File directory
$BaseDir = '\\MyFileServer\MyDirectory'


$Query = "EXEC dbo.usp_MyQuery '$BizUnit'"
$TargetFile = "MyTargetFile_" + $BizUnit + "_$DateStamp.xml"
$TargetDir = (Join-Path -Path $BaseDir -ChildPath $BizUnit)

$Query | Out-File -FilePath (Join-Path -Path $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"

When the job is run, it fails with this error message:

A job step received an error at line 14 in a PowerShell script. The corresponding line is '$Query | Out-File -FilePath (Join-Path -Path $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot perform operation because operation "ReportWrongProviderType" is invalid. Remove operation "ReportWrongProviderType", or investigate why it is not valid.

I can't find any references to "ReportWrongProviderType" when I search the interwebz. Any ideas on what is wrong with the Out-File call?

peterk411
  • 947
  • 1
  • 9
  • 14
  • What OS version are your running on? –  Oct 21 '13 at 07:35
  • Can you provide the "FullyQualifiedErrorId" from the error output? You might also add to the end of the line "-ErrorVariable e" and then on the next line "$e | select *". This will output the full error info that might provide more information. –  Oct 21 '13 at 08:10
  • I've updated the question text with the operating system version change. I ran the job with the ErrorVariable as suggested, however the error message text remained the same, even after setting $ErrorActionPreference = 'Continue'. – peterk411 Oct 22 '13 at 15:25
  • Adding a try/catch block, I captured the error output: A job step received an error at line 28 in a PowerShell script. The corresponding line is ' throw $e'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'System.Management.Automation.CmdletInvocationException: Cannot open file because the current provider (SqlServer) cannot open a file. The account running the job has Full Control of the target directory. Perhaps there is a missing permission that would allow Powershell to act on a file share in Win2012? – peterk411 Oct 22 '13 at 15:40
  • 1
    Check out my answer on this question: http://dba.stackexchange.com/questions/51870/error-running-powershell-script-in-sql-server-agent/51885?noredirect=1#comment91978_51885 –  Oct 22 '13 at 15:42

1 Answers1

21

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

peterk411
  • 947
  • 1
  • 9
  • 14