Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

powershellsql-server-2012sql-server-agent

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?

Best Answer

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.

Related Question