Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What OS version are your running on? –  Shawn Melton Oct 21 '13 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. –  Shawn Melton Oct 21 '13 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 '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: dba.stackexchange.com/questions/51870/… –  Shawn Melton Oct 22 '13 at 15:42
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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