PowerShell脚本插入Oracle时SERVER_NAME非空错误排查请求

云上老周

2026-6-21

问题

我编写了一段PowerShell脚本，用于收集SQL Server实例相关信息并写入作为环境信息仓库的Oracle数据库。执行脚本时，系统抛出错误提示Column SERVER_NAME cannot be null，但输出对应变量时可以看到ServerName字段存在有效值。已确认Oracle连接正常，操作账号具备足够的插入/更新权限，请求帮忙排查脚本中的错误原因。

相关脚本

#Import necessary modules
Import-Module DBAtools

# Path to the Oracle.ManagedDataAccess.dll file
$oracleDllPath = "D:\oracle\product\12.2.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
# Load the assembly
Add-Type -Path $oracleDllPath

# Initialize logging
$logFile = "D:\PowerShell\log.txt"
Start-Transcript -Path $logFile

WRITE-HOST "WE ARE HERE-1"
try {
    # Step 1: Get all instance names from Central Management Studio and place them in a variable called $Instances
    #$CMSInstance = "CentralManagementServerInstanceName"
    #$Instances = Get-DbaCmsRegServer -SqlInstance $CMSInstance -Group "" | Select-Object -ExpandProperty Name
    $Instances = "SQLInst1","SQLInst2"

    # Initialize arrays to hold accessible and inaccessible instances
    $AccessibleInstances = @()
    $InaccessibleInstances = @()

    # Step 2: Ensure each instance is accessible
    foreach ($Instance in $Instances) {
        try {
            # Check if the instance is accessible
            Test-DbaConnection -SqlInstance $Instance -ErrorAction Stop
            # If successful, add to AccessibleInstances
            $AccessibleInstances += $Instance
        } catch {
            # If not accessible, add to InaccessibleInstances
            $InaccessibleInstances += $Instance
        }
    }
WRITE-HOST "WE ARE HERE-2"
    # Initialize arrays to hold data
    $serverStageData = @()
    $diskDriveStageData = @()

    # Step 5: Loop through each entry in $AccessibleInstances and gather required information
    foreach ($Instance in $AccessibleInstances) {
        # Example DBAtools commands (replace with actual commands as needed)
        $server = Connect-DbaInstance -SqlInstance $Instance

        # Gathering Operating System Information
        $os = Get-DbaOperatingSystem -ComputerName $server.ComputerName
        $computerSystem = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $server.ComputerName
        $cpu = Get-CimInstance -ClassName Win32_Processor -ComputerName $server.ComputerName
        $networkConfig = Get-CimInstance -ClassName Win32_NetworkAdapterConfiguration -ComputerName $server.ComputerName | Where-Object { $_.IPAddress -ne $null } 
WRITE-HOST "WE ARE HERE-3"
        # Creating the output object
        $serverInfo = [PSCustomObject]@{
            ServerName               = $server.ComputerName
            OSName                   = $os.OSVersion
            OSVersionCode            = $os.Version
            OSVendorName             = $os.Manufacturer
            OSPatchLevelCode         = $os.Build
            CPUVendorName            = $cpu.Name[0]
            CPUManufacturerName      = $cpu.Manufacturer[0]
            CPUFrequencyMSR          = $cpu.MaxClockSpeed[0]
            PhysicalCoreCount        = ($cpu | Measure-Object -Property NumberOfCores -Sum).Sum
            TotalCoreCount           = ($cpu | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum
            PhysicalRAMGBMSR         = [math]::round($computerSystem.TotalPhysicalMemory / 1GB, 2)
            RAMGBMSR                 = [math]::round($computerSystem.TotalPhysicalMemory / 1GB, 2)
            DomainName               = $computerSystem.Domain
            OperationalStatusCode    = $computerSystem.Status
            ManagedByServerName      = 'CMS'
            IPAddress                = $networkConfig.IPAddress[0]
            MANAGED_BY_DATABASE_NAME = 'DBAAdmin'
        }
WRITE-HOST "WE ARE HERE-4"
        #2.TABLE DISK_DRIVE_STAGE
        $diskDriveInfo = Get-DbaDiskSpace -ComputerName $server.ComputerName | Select-Object @{label='SERVER_NAME';expression={$_.ComputerName}}, @{label='DISK_DRIVE_NAME';expression={$_.Label}}, @{label='CAPACITY_GB_MSR';expression={$_.SizeInGB}}, @{label='USED_GB_MSR';expression={$_.SizeInGB - $_.FreeInGB}}, @{label='FREE_SPACE_GB_MSR';expression={$_.FreeInGB}}, @{label='DISK_DRIVE_LETTER_CODE';expression={$_.Name}}, @{label='LINUX_MOUNT_POINT_PATH';expression={'N/A'}} 

        # Store the gathered information in the respective variables
        $serverStageData += $serverInfo
        #$diskDriveStageData += $diskDriveInfo
        
    }
    

    # Step 6: Connect to the Oracle database
    $oracleConnString = "User Id=myuser;Password=mypwd;Data Source=myoracledb"
    $conn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($oracleConnString)
WRITE-HOST "WE ARE HERE-5"
    try {
        $conn.Open()
WRITE-HOST "WE ARE HERE-6"
        Write-Host "Successfully connected to Oracle database."
    } catch {
WRITE-HOST "WE ARE HERE-7"
        Write-Host "Failed to connect to Oracle database. Exiting script."
        Stop-Transcript
        Exit 1
    }
WRITE-HOST "WE ARE HERE-8"
    # Step 7: Function to update or insert records in Oracle tables.
function Upsert-OracleTable {
    param (
        [string]$tableName,
        [array]$data,
        [array]$columns
    )

        foreach ($record in $data) {
            # Ensure no null values are inserted by checking each value
            foreach ($column in $columns) {
                if ([string]::IsNullOrEmpty($record.$column)) {
                    Write-Host "Column $column cannot be null. Record: $record"
                    throw "Column $column cannot be null"
                }
            }

            # Build the query to check if the record exists by comparing all columns
            $whereClause = @()
            foreach ($column in $columns) {
                $whereClause += "$column = :$column"
            }
            $whereClause = $whereClause -join " AND "

            $checkCmd = $conn.CreateCommand()
            $checkCmd.CommandText = "SELECT COUNT(*) FROM $tableName WHERE $whereClause"
            $checkCmd.Parameters.Clear()
            foreach ($column in $columns) {
                $checkCmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter($column, $record.$column)))
            }
            $exists = $checkCmd.ExecuteScalar()

            if ($exists -ne 0) {
                # Build the update clause
                $updateClause = ""
                $updateNeeded = $false
                foreach ($column in $columns) {
                    $currentValueCmd = $conn.CreateCommand()
                    $currentValueCmd.CommandText = "SELECT $column FROM $tableName WHERE $whereClause"
                    $currentValueCmd.Parameters.Clear()
                    foreach ($col in $columns) {
                        $currentValueCmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter($col, $record.$col)))
                    }
                    $currentValue = $currentValueCmd.ExecuteScalar()

                    if ($currentValue -ne $record.$column) {
                        $updateClause += "$column = :$column, "
                        $updateNeeded = $true
                    }
                }

                if ($updateNeeded) {
                    # Remove the last comma
                    $updateClause = $updateClause.Substring(0, $updateClause.Length - 2)

                    # Build and execute the update command
                    $updateCmd = $conn.CreateCommand()
                    $updateCmd.CommandText = "UPDATE $tableName SET $updateClause WHERE $whereClause"
                    $updateCmd.Parameters.Clear()
                    foreach ($column in $columns) {
                        $updateCmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter($column, $record.$column)))
                    }
                    $updateCmd.ExecuteNonQuery()
                }
            } else {
                # Build and execute the insert command
                $columnsStr = ""
                $valuesStr = ""
                $insertCmd = $conn.CreateCommand()
                foreach ($column in $columns) {
                    $columnsStr += "$column, "
                    $valuesStr += ":$column, "
                    $insertCmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter($column, $record.$column)))
                }

                # Remove the last commas
                $columnsStr = $columnsStr.Substring(0, $columnsStr.Length - 2)
                $valuesStr = $valuesStr.Substring(0, $valuesStr.Length - 2)

                $insertCmd.CommandText = "INSERT INTO $tableName ($columnsStr) VALUES ($valuesStr)"
                Write-Host "Insert Command: $insertCmd.CommandText"
                foreach ($param in $insertCmd.Parameters) {
                    Write-Host "Parameter: $($param.ParameterName) = $($param.Value)"
                }
                $insertCmd.ExecuteNonQuery()
            }
        }
    }



WRITE-HOST "WE ARE HERE-14"
    # Update or insert records in Oracle tables with specific columns
    $serverColumns = @("SERVER_NAME", "OPERATING_SYSTEM_NAME", "OPERATING_SYSTEM_VERSION_CODE", "OPERATING_SYSTEM_VENDOR_NAME", "OPERATING_SYSTEM_PTCH_LVL_CODE", "CPU_VENDOR_NAME", "CPU_MANUFACTURER_NAME", "CPU_FREQUENCY_MSR", "PHYSICAL_CORE_CNT", "TOTAL_CORE_CNT", "PHYSICAL_RAM_GB_MSR", "RAM_GB_MSR", "DOMAIN_NAME", "OPERATIONAL_STATUS_CODE", "MANAGED_BY_SERVER_NAME", "IP_ADDRESS", "MANAGED_BY_DATABASE_NAME")
    #$diskDriveColumns = @("SERVER_NAME", "DISK_DRIVE_NAME", "CAPACITY_GB_MSR", "FREE_SPACE_GB_MSR", "DISK_DRIVE_LETTER_CODE", "LINUX_MOUNT_POINT_PATH")
WRITE-HOST "WE ARE HERE-15"
    # Specify the schema name for each table
    $serverTable = "MySchema.SERVER_STAGE_T"
   # $diskDriveTable = "MyShema.DISK_DRIVE_STAGE_T"
WRITE-HOST "WE ARE HERE-16" 

echo $serverColumns
echo $serverStageData
echo $serverTable
   
    Upsert-OracleTable -tableName $serverTable -data $serverStageData -columns $serverColumns
    #Upsert-OracleTable -tableName $diskDriveTable -data $diskDriveStageData -columns $diskDriveColumns
WRITE-HOST "WE ARE HERE-17"
} catch {
    Write-Host "An error occurred: $_"
    Stop-Transcript
    Exit 1
} finally {
    # Close the Oracle connection
    $conn.Close()
    Stop-Transcript
}

错误日志

Column SERVER_NAME cannot be null.
Record: @{ServerName=Server1; OSName=Microsoft Windows Server 2016 Standard; OSVersionCode=10.0.14393; OSVendorName=Microsoft Corporation; OSPatchLevelCode=14393; CPUVendorName=Intel(R) Xeon(R) Platinum 8170 CPU @ 2.10GHz; CPUManufacturerName=GenuineIntel; CPUFrequencyMSR=2095; PhysicalCoreCount=4; TotalCoreCount=4; PhysicalRAMGBMSR=48; RAMGBMSR=48; DomainName=my.domain; OperationalStatusCode=OK; ManagedByServerName=CMSServer; IPAddress=12.11.196.258; MANAGED_BY_DATABASE_NAME=DBAAdmin}
PS>TerminatingError(): "Column SERVER_NAME cannot be null"
An error occurred: Column SERVER_NAME cannot be null

问题原因与解决方案

核心原因

脚本存在属性名与Oracle列名不匹配的问题：

构建的$serverInfo对象中，服务器名称的属性是ServerName（驼峰命名）
传入Upsert-OracleTable函数的列名数组$serverColumns里，对应的Oracle列是SERVER_NAME（大写下划线命名）
函数通过$record.$column取值时，会尝试访问对象的SERVER_NAME属性，但该属性不存在，返回null，触发非空校验错误。

修复方案

方案1：修改PS对象属性名匹配Oracle列名

直接调整$serverInfo的属性名，与Oracle列名完全一致：

$serverInfo = [PSCustomObject]@{
    SERVER_NAME               = $server.ComputerName
    OPERATING_SYSTEM_NAME     = $os.OSVersion
    OPERATING_SYSTEM_VERSION_CODE = $os.Version
    OPERATING_SYSTEM_VENDOR_NAME = $os.Manufacturer
    OPERATING_SYSTEM_PTCH_LVL_CODE = $os.Build
    CPU_VENDOR_NAME            = $cpu.Name[0]
    CPU_MANUFACTURER_NAME      = $cpu.Manufacturer[0]
    CPU_FREQUENCY_MSR          = $cpu.MaxClockSpeed[0]
    PHYSICAL_CORE_CNT        = ($cpu | Measure-Object -Property NumberOfCores -Sum).Sum
    TOTAL_CORE_CNT           = ($cpu | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum
    PHYSICAL_RAM_GB_MSR         = [math]::round($computerSystem.TotalPhysicalMemory / 1GB, 2)
    RAM_GB_MSR                 = [math]::round($computerSystem.TotalPhysicalMemory / 1GB, 2)
    DOMAIN_NAME               = $computerSystem.Domain
    OPERATIONAL_STATUS_CODE    = $computerSystem.Status
    MANAGED_BY_SERVER_NAME      = 'CMS'
    IP_ADDRESS                = $networkConfig.IPAddress[0]
    MANAGED_BY_DATABASE_NAME = 'DBAAdmin'
}

方案2：在Upsert函数中添加属性名映射

如果不想修改PS对象结构，可在函数内添加映射逻辑，将Oracle列名对应到PS对象属性：

# 在Upsert-OracleTable函数的foreach ($record in $data)循环前添加映射表
$columnMapping = @{
    "SERVER_NAME" = "ServerName"
    "OPERATING_SYSTEM_NAME" = "OSName"
    "OPERATING_SYSTEM_VERSION_CODE" = "OSVersionCode"
    "OPERATING_SYSTEM_VENDOR_NAME" = "OSVendorName"
    "OPERATING_SYSTEM_PTCH_LVL_CODE" = "OSPatchLevelCode"
    "CPU_VENDOR_NAME" = "CPUVendorName"
    "CPU_MANUFACTURER_NAME" = "CPUManufacturerName"
    "CPU_FREQUENCY_MSR" = "CPUFrequencyMSR"
    "PHYSICAL_CORE_CNT" = "PhysicalCoreCount"
    "TOTAL_CORE_CNT" = "TotalCoreCount"
    "PHYSICAL_RAM_GB_MSR" = "PhysicalRAMGBMSR"
    "RAM_GB_MSR" = "RAMGBMSR"
    "DOMAIN_NAME" = "DomainName"
    "OPERATIONAL_STATUS_CODE" = "OperationalStatusCode"
    "MANAGED_BY_SERVER_NAME" = "ManagedByServerName"
    "IP_ADDRESS" = "IPAddress"
    "MANAGED_BY_DATABASE_NAME" = "MANAGED_BY_DATABASE_NAME"
}

# 替换原有的非空校验逻辑
foreach ($column in $columns) {
    $propertyName = $columnMapping[$column] ?? $column
    if ([string]::IsNullOrEmpty($record.$propertyName)) {
        Write-Host "Column $column cannot be null. Record: $record"
        throw "Column $column cannot be null"
    }
}

# 后续所有使用$record.$column的地方，都替换为$record.$propertyName

额外建议

可在脚本中添加属性名校验逻辑，提前发现不匹配问题，避免运行时错误
统一使用一种命名规范（如全大写下划线），减少此类映射错误

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

PowerShell脚本插入Oracle时SERVER_NAME非空错误排查请求

问题 #

相关脚本 #

错误日志 #

问题原因与解决方案 #

核心原因 #

修复方案 #