Powershell sql insert error data will be truncated removed data and still get error

Question

I have a powershell script that I am trying to use to write data from a source .txt to an SQL table

Import-Csv $_.fullname -Header Z_AP_EXNUM,Z_AP_ID | ForEach-Object {Invoke-Sqlcmd `
        -Database $database -ServerInstance $server -Username $uid -Password $pwd `
        -Query "insert into $table VALUES ('$_.Z_AP_EXNUM','$_.Z_AP_ID')"

My actual code has about 293 headers/columns to insert. The error I receiving is that String or binary data will be truncated. The statement has been terminated. I went into one of my test files and removed all the data but a few standard fields and still receive these errors. I am not sure what could be causing the insert to fail.

Error:

Invoke-Sqlcmd : String or binary data would be truncated. The statement has been terminated. At C:\Users***\Desktop\script\scripy.ps1:79 char:111 + Z_AP_STR_CSEM5_9,Z_AP_STR_NAME5_10,Z_AP_STR_SCOR5_10,Z_AP_STR_CSEM5_10 | ForEach-Object {Invoke-Sqlcmd <<<< ` + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

majkinetor majkinetor · Accepted Answer · 2016-04-29T19:00:36

Check the table schema, this error usually shows due to the difference of column definition type in the table and the powershell data type you want to store in it.

Powershell sql insert error data will be truncated removed data and still get error

1 Answers