can someone please point me where I made a mistake in below PowerShell script? I'm trying to gather SQL Server related info and load the info into an oracle database which is our repository for keeping the environment related info. The script is spitting error that column Server_Name cannot be null during the update/insert (see log) but when I echo the variable it does contain the Server_Name column value. Perhaps my way of doing the data load is wrong or perhaps there is a syntax error somewhere ? Any help would be appreciated.
#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
}
Connection to oracle is working fine
User have enough privileges to insert/update data in the table.
Error: 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
The problem is that your $serverStageData object is made up of elements containing these keys:
$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'
}
And you are attempting to insert into a table using these column names:
$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")
The code fails because the names of the properties of the $serverInfo object do not match the $serverColumns that you provide. Update the code so that they match, which may require you to also update the underlying Oracle table which you are inserting data into.
The error you're seeing is due to this part of the function:
if ([string]::IsNullOrEmpty($record.$column)) {
Write-Host "Column $column cannot be null. Record: $record"
throw "Column $column cannot be null"
}
Your error message shows:
Record: @{ServerName=Server1;...
Column SERVER_NAME cannot be null
In other words:
"ServerName" <> "SERVER_NAME"
The following short snippet of code reproduces the error:
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"
}
}
}
}
$serverStageData = @([PSCustomObject]@{"ServerName"="Server1"})
echo $serverStageData
Upsert-OracleTable `
-data $serverStageData `
-columns @("SERVER_NAME")