PowerShell脚本在SQL Agent执行报ReportWrongProviderType错误，需切换至PS7

问题现象 #

我的PowerShell脚本在本地PowerShell命令行运行正常，但在SQL Agent中执行时抛出以下错误：

Executed as user: DOMAIN\svcSQLDefaultPROD. A job step received
an error at line 21 in a PowerShell script. The corresponding line is
' Export-Csv -Path $outputFile -NoTypeInformation -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. '. Process Exit Code -1. The step failed.

我尝试过两种作业步骤类型：

• 直接选择powershell类型
• 改为CmdExe类型，但命令行无法实现PowerShell的CSV导出逻辑

另外，作业完全未正常运行，甚至连日志文件都没有生成。

脚本内容 #

# Log file path
$logFile = "C:\RNA\ExportRNAData.log"
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
"$timestamp - Script started" | Out-File -FilePath $logFile -Append

# Define SQL Server connection details
$sqlServerInstance = "SourceServerName"
$databaseName = "DBName1"
$storedProcedure = "[DBName2].[dbo].[GenExportFile_tstHK]"

# Define the output folder
$outputFolder = "\\RemoteServerName\Export"

try {
    # Retrieve the list of Branch values from the SQL Server table
    $branches = Invoke-Sqlcmd  -Query "SELECT Branch FROM Sites" -ServerInstance $sqlServerInstance -Database $databaseName

    # Iterate through each Branch value
    foreach ($branch in $branches) {
        $areaID = $branch.Branch
        $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
        $outputFile = "$outputFolder\PowershellHK_2_$areaID`_$timestamp.csv"

        # Execute the stored procedure with the current AreaID
        Invoke-Sqlcmd -Query "EXEC $storedProcedure @AreaID='$areaID'" -ServerInstance $sqlServerInstance |
        Export-Csv -Path $outputFile -NoTypeInformation -Encoding UTF8BOM -UseQuotes AsNeeded

        "$timestamp - Results for AreaID '$areaID' exported to $outputFile" | Out-File -FilePath $logFile -Append
    }
} catch {
    "$timestamp - Error: $_" | Out-File -FilePath $logFile -Append
}

"$timestamp - Script completed" | Out-File -FilePath $logFile -Append

# set permissions
# Set-ExecutionPolicy RemoteSigned -Scope Process

已确认的配置 #

• SQL Agent服务账户DOMAIN\svcSQLDefaultPROD已被授予远程服务器文件夹\\RemoteServerName\Export及本地文件夹C:\RNA的完全权限
• 为使用PowerShell 4不支持的-UseQuotes AsNeeded参数，已安装PowerShell 7，但推测SQL Agent仍在调用旧版本PowerShell执行脚本

需要解决的问题 #

1. 如何配置SQL Agent，使其使用PowerShell 7来执行脚本？
2. 为什么作业完全未运行，连日志文件都无法生成？

内容的提问来源于stack exchange，提问作者PrettyCode