Control-M执行PowerShell脚本时SQL读取异常问题排查

• 运行环境：通过Control-M调度执行PowerShell脚本，脚本核心功能为运行SQL脚本生成检查结果表，并将结果通过邮件发送
• 异常表现：仅在Control-M调度执行时，约每隔一次就会出现第一个表（整体检查结果表）异常：该表基于其他表的NStatus值返回Overall checks are RED/AMBER/GREEN，但当表显示RED时，邮件主题并未同步更新为RED；手动直接运行.ps1文件时无此问题
• 核心逻辑：整体检查结果由SQL脚本根据其他检查表的NStatus聚合生成

1. 会话资源复用导致的残留数据
   Control-M的作业执行可能复用PowerShell或SQL连接会话，隔次执行时读取到上一次的残留数据（比如未清空的变量、SQL查询缓存），导致整体状态判断异常。手动运行时每次都是全新会话，不会有残留问题。

2. 变量未正确初始化
   脚本中存储整体检查结果的变量，在Control-M环境下未在每次执行前重置，隔次执行时沿用了上一次的旧值，导致邮件主题与实际表结果不符。

3. SQL执行顺序或数据一致性问题
   生成整体检查结果的SQL可能先于其他检查表的SQL完成执行，读取到未更新的NStatus值；手动运行时执行顺序更稳定，不会出现此问题。

4. Control-M作业配置异常
   比如作业开启了会话复用、并发执行或自动重试，导致隔次执行的环境（如权限、资源）不一致，引发异常。

1. 强制重置变量与会话资源 #

在脚本开头添加重置逻辑，确保每次执行时关键变量和资源都处于初始状态：

# 重置所有关键变量
$overallStatus = $null
$mailSubject = "Check Result - PENDING"

# 销毁并重建SQL连接（如果使用持久连接）
if ($sqlConnection) {
    $sqlConnection.Close()
    $sqlConnection.Dispose()
    $sqlConnection = $null
}

2. 保证SQL执行的一致性 #

修改SQL脚本，用事务包裹所有检查逻辑，确保整体检查在所有单表检查完成后执行：

BEGIN TRANSACTION
-- 先执行所有单表检查逻辑
EXEC dbo.ExecuteIndividualChecks
-- 再执行整体检查逻辑
SELECT 'Overall checks are ' + CASE 
    WHEN EXISTS(SELECT 1 FROM CheckResults WHERE NStatus = 'RED') THEN 'RED'
    WHEN EXISTS(SELECT 1 FROM CheckResults WHERE NStatus = 'AMBER') THEN 'AMBER'
    ELSE 'GREEN'
END AS OverallResult
COMMIT TRANSACTION

3. 调整Control-M作业配置 #

• 关闭会话复用功能（若Control-M有相关设置），确保每次作业都使用全新的执行会话
• 检查作业执行用户的权限，确保其与手动运行脚本的用户拥有相同的SQL访问权限
• 禁用作业的自动重试机制，避免重试导致的重复执行干扰

4. 添加调试日志 #

在脚本中添加详细日志，记录关键节点的变量值与执行结果，便于定位异常：

# 记录执行起始时间
Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] Script started"

# 执行整体检查SQL并记录结果
$overallCheckResult = Invoke-SqlCmd -ServerInstance "YourSQLServer" -Database "YourDB" -Query $overallCheckSql
Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] Overall check result: $($overallCheckResult.OverallResult)"

# 生成邮件主题并记录
$mailSubject = "Check Result - $($overallCheckResult.OverallResult.Split(' ')[-1])"
Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] Mail subject: $mailSubject"

在Control-M中配置捕获脚本输出日志，方便异常时排查。

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