powershell error: ‘Cannot perform operation because operation “ReportWrongProviderType” is invalid’

I got the following error from a Powershell script I wrote to run SQLExress backups on a remote server.

The script runs on a Standard Edition server as a SQL Agent job. It creates and then runs a T-sql script on the remote server to backup all the databases to a folder on the remote server.

The error occurred when I migrated the job from a Windows 2003 server running SQL 2008 R2 to a Windows 2012 Server running SQL 2012

Message
A job step received an error at line 18 in a PowerShell script. The corresponding line is 'invoke-sqlcmd -outputsqlerrors $True -ServerInstance $ServerInstance -QueryTimeout 3600 -InputFile d:\dbawork\admin\bin\sp_BackupDatabases.sql > $SqlOutputLog'. 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.

The problem was that the Powershell didn’t like the log output being re-directed to a UNC path.

So I changed this:

$SqlOutputLog =  "\\server1\d$\sql_backup\remote_backups\log\" + $ServerInstance.replace("\", "_") + "_sql_output.log"
invoke-sqlcmd -outputsqlerrors $True -ServerInstance $ServerInstance -QueryTimeout 3600 -InputFile d:\dbawork\admin\bin\sp_BackupDatabases.sql > $SqlOutputLog
```

to this

$SqlOutputLog = "d:\sql_backup\remote_backups\log\" + $ServerInstance.replace("\", "_") + "_sql_output.log"
invoke-sqlcmd -outputsqlerrors $True -ServerInstance $ServerInstance -QueryTimeout 3600 -InputFile d:\dbawork\admin\bin\sp_BackupDatabases.sql > $SqlOutputLog
““
i.e. I changed ‘\server1\d$’ to ‘d:’

I’m not sure what the root cause here was, to be honest. If time ever allows I’ll do some investigation.

Advertisements