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?

Improve this question
5
  • What OS version are your running on?
    – user507
    Oct 21, 2013 at 7: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.
    – user507
    Oct 21, 2013 at 8: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, 2013 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, 2013 at 15:40
  • 1
    Check out my answer on this question: dba.stackexchange.com/questions/51870/…
    – user507
    Oct 22, 2013 at 15:42

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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