执行PowerShell脚本从Splunk导入数据至SQL Server时遇ExecuteNonQuery截断错误的解决建议请求

Hey there, let's break down how to fix this truncation error you're seeing. The message Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated. The statement has been terminated." means one of the values you're trying to insert is longer than the maximum length defined for its target column in your SQL Server table. Let's walk through the diagnosis and fixes step by step.

First: Diagnose the Root Cause #

Start by checking the column definitions of your dbo.SPK_ITMetricsChart table. Run this SQL query to get the exact constraints:

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SPK_ITMetricsChart' AND TABLE_SCHEMA = 'dbo';

Compare the results to the values you're inserting:

• [Site] column: You're inserting F10N (4 characters), F11 (3), F15 (3). If this column is defined as VARCHAR(3), F10N will get truncated immediately. This is the most likely culprit.
• [Category] column: You're inserting Delivery (7 characters). If the column's length is set to VARCHAR(6) or shorter, this will trigger the error.
• [Value] column: Your values are 2.312/3.212/6.412 (5 characters each). If the column is VARCHAR(4) or shorter, it will truncate. For numeric values, it's better to use a DECIMAL type (like DECIMAL(5,3)) instead of string types to avoid length issues entirely.
• [Datetime] column: You mentioned the format matches, but double-check if it's a DATETIME/DATETIME2 type (not a string type). If it is a string column, ensure its length is at least 19 to fit yyyy-mm-dd hh:mm:ss.

Fix 1: Adjust Table Column Lengths #

If any column is too short, alter the table to increase its length. For example, to fix the [Site] column:

ALTER TABLE dbo.SPK_ITMetricsChart ALTER COLUMN Site VARCHAR(4) NOT NULL;

Adjust the length and NOT NULL constraint based on your actual table requirements.

Fix 2: Rewrite Your Script with Parameterized Queries (Highly Recommended) #

Your current script uses string concatenation to build SQL queries, which is risky (SQL injection) and prone to formatting/length errors. Switching to parameterized queries will eliminate these issues and make your code more robust. Here's the revised script:

param ($jsonContent)
$jsonObj = $jsonContent | ConvertFrom-Json
write-host $jsonObj.result.F10
write-host $jsonObj.result.F11
write-host $jsonObj.result.F15
write-host $jsonObj.result.datetime

$serverName = "FSMSSTEST132,3067"
$databaseName = "ITReport"
$tableName = "dbo.SPK_ITMetricsChart"
$YourUserID = "MES123"; $YourPassword="Asd123";

# Organize your data into a structured array for easy looping
$metricData = @(
    @{ MetricValue = $jsonObj.result.F10; SiteCode = "F10N" },
    @{ MetricValue = $jsonObj.result.F11; SiteCode = "F11" },
    @{ MetricValue = $jsonObj.result.F15; SiteCode = "F15" }
)
$category = "Delivery"
$reportDatetime = [DateTime]$jsonObj.result.datetime

# Set up SQL connection
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';User ID='$YourUserID';Password='$YourPassword'"
$Connection.Open()

# Create parameterized insert command
$insertQuery = "INSERT INTO $tableName ([Datetime],[Value],[Category],[Site]) VALUES (@Datetime, @Value, @Category, @Site)"
$Command = New-Object System.Data.SQLClient.SQLCommand($insertQuery, $Connection)

# Define parameters (match types/lengths to your actual table columns)
$Command.Parameters.Add("@Datetime", [System.Data.SqlDbType]::DateTime) | Out-Null
$Command.Parameters.Add("@Value", [System.Data.SqlDbType]::VarChar, 10) | Out-Null # Adjust length to match your column
$Command.Parameters.Add("@Category", [System.Data.SqlDbType]::VarChar, 10) | Out-Null
$Command.Parameters.Add("@Site", [System.Data.SqlDbType]::VarChar, 4) | Out-Null # Match adjusted column length

# Set static parameter values once
$Command.Parameters["@Datetime"].Value = $reportDatetime
$Command.Parameters["@Category"].Value = $category

# Loop through metrics and insert
foreach ($data in $metricData) {
    $Command.Parameters["@Value"].Value = $data.MetricValue
    $Command.Parameters["@Site"].Value = $data.SiteCode
    $Command.ExecuteNonQuery()
}

$Connection.Close();

Bonus: If [Value] is a Numeric Column #

If you change the [Value] column to DECIMAL(5,3) (to store values like 2.312), update the parameter definition to:

$Command.Parameters.Add("@Value", [System.Data.SqlDbType]::Decimal) | Out-Null
$Command.Parameters["@Value"].Precision = 5
$Command.Parameters["@Value"].Scale = 3

Final Checks #

1. After adjusting the table columns, re-run your (revised) script.
2. If you still get the error, double-check that all values match their target column's constraints (no extra whitespace, correct data types, etc.).

内容的提问来源于stack exchange，提问作者Pann Phyu Phway