SQL Server Agent执行PowerShell脚本无法填充数据表求助

一、脚本逻辑致命错误（最可能的根因） #

你的脚本存在变量定义顺序错误：

# 此时$results还未赋值，$Query中的$results是空字符串
$Query = "EXEC dbo.GET_DeviceMappings '$results'"

# 后续才对$results赋值
$results = GetDeviceMappings -Uri 'https://my-url.com/v1/devicemappings' -Method 'Get' -Headers $headers -ContentType 'application/json' 

PowerShell是顺序执行的，定义$Query时$results尚未被赋值，因此最终执行的SQL语句是EXEC dbo.GET_DeviceMappings ''，传递空参数给存储过程，自然不会写入数据。

本地运行正常是因为你可能在同一个PowerShell会话中曾执行过脚本，$results残留了旧值；而SQL Server Agent每次都是全新会话，$results初始为空，导致参数无效。

修复方法：将$Query的定义移到$results赋值之后：

$results = GetDeviceMappings -Uri 'https://my-url.com/v1/devicemappings' -Method 'Get' -Headers $headers -ContentType 'application/json' 
# 此时$results已有值，再构造SQL语句
$Query = "EXEC dbo.GET_DeviceMappings '$results'"

二、权限与上下文问题 #

即使修复了脚本顺序，仍需排查以下点：

• SQL Server权限：检查SQLProxy账户是否拥有：
  • SQL Server的登录权限（需映射到Staging数据库的对应用户）
  • dbo.GET_DeviceMappings存储过程的执行权限
  • 目标数据表的INSERT/UPDATE权限
• API访问权限：SQLProxy账户是否能访问API地址和代理服务器http://web-proxy.part.com:3128，是否存在防火墙/组策略限制该账户的网络访问
• 模块加载权限：SQLProxy账户是否有权限加载SqlServer模块，可在脚本开头添加Write-Host "Loaded modules: $(Get-Module)"输出日志验证

三、错误捕获与日志缺失 #

你的脚本错误捕获能力不足，SQL Agent执行时的错误无法直接查看，需添加日志输出：

• 在Catch块中写入错误日志到本地文件：

  Catch
  {
      $errorMsg = "$(Get-Date): $($error[0].exception.Message)"
      Add-Content -Path "C:\Logs\API_Script_Error.log" -Value $errorMsg
      Write-Host $errorMsg
  }
  

• 在Invoke-Sqlcmd后添加执行结果日志：

  $sqlResult = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query $Query -TrustServerCertificate
  Add-Content -Path "C:\Logs\SQL_Execution.log" -Value "$(Get-Date): SQL执行结果：$($sqlResult | Out-String)"
  

• 确保SQLProxy账户有写入日志目录的权限

四、SQL语句转义问题 #

如果$results包含单引号，直接拼接SQL语句会导致语法错误，建议使用参数化查询避免注入和语法问题：

# 使用参数化查询，无需手动转义单引号
$params = @{
    ServerInstance = $SQLServer
    Database = $Database
    Query = "EXEC dbo.GET_DeviceMappings @results"
    TrustServerCertificate = $true
    SqlParameters = @{ results = $results }
}
Invoke-Sqlcmd @params

五、代理与网络上下文差异 #

本地用户和SQLProxy账户的系统代理配置可能不同，需确保SQLProxy账户能正常访问指定的代理服务器（你的脚本已显式指定-Proxy $proxy，但需验证该账户的网络连通性）

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