1

I wrote this query in SQL, and trying to paste it over to Powershell to run on command, however, I getting the error:

Exception calling "ExecuteReader" with "0" argument(s): "String or binary data would be truncated.

Function CreateCall ($accountno)
{

Write-Verbose "Loading"

write-host $accountno

    $server = 'OMIT'
    $database = 'OMIT'
    $user = 'OMIT'
    $pwd = 'OMIT'
    $connectionString = “Server=$server;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

    $connection.Open()

    $DBNull = [System.DBNull]::Value 
    $date = Get-Date -f "yyyy-MM-dd hh:mm:ss"

    $query3 = "INSERT INTO TABLEC (call_number, callstatus, callcode, calltype, debtor_no, account_no, occupant_code, createdon, createdby, priority, assignto,
dept_route, scheduledon, completedon, completedby, systemcomplete, note, order_no, released, ordercode, requiredon, callername, phone, postbillto, pseudo, printed,
workorder, task_, asset, reference, linktocall, email)
SELECT call_number = (select lastserial+1 from TABLEB where tablename = 'TABLEB'), 
callstatus='S', callcode='ADMIN', calltype='SERORD', debtor_no, account_no, occupant_code, createdon='$date', createdby='autotool', priority=NULL, assignto=112,
dept_route=NULL, scheduledon='$date', completedon=NULL, completedby=NULL, systemcomplete=NULL, note='Variance Pap Call Out', order_no=(select lastjournal+1 FROM TABLEA where source = 'SO'), 
released=0, ordercode='SERORD', requiredon='$date', callername=name, phone=home_phone, postbillto=1, pseudo=NULL, printed=0, workorder=NULL, task_=NULL, asset=NULL, reference=NULL, linktocall=NULL, email=NULL
FROM TABLED
where account_no = '$accountno'"

    $command = $connection.CreateCommand()

        $command.CommandText = $query3

        $result = $command.ExecuteReader()

        $connection.Close()

        Write-Verbose "Done one"
}

I've tried changing the NULLs into $DBNULL which = [System.DBNull]::Value

Do some of the NULLs need '' and some not, depending on if DateTime, or INT value?

Thanks.

Simon Yo
  • 101
  • 12
  • Does the insert script run without errors in SQL Server or does it error out as well? – Shaneis May 30 '17 at 14:20
  • Runs without error in SQL Server @Shaneis – Simon Yo May 30 '17 at 14:24
  • 1
    So you can take `$query3|clip` and paste that into SSMS and it works fine? I suspect you would get the same error. – Matt May 30 '17 at 14:26
  • 1
    That message is coming from SQL and is referring to one of the columns being truncated. https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated. This is a tempting dupe. I don't know if we can tell you exactly where the issue is coming from. – Matt May 30 '17 at 14:26
  • 1
    Truncation error means your data is too wide for your columns. – Jacob H May 30 '17 at 14:27
  • I flagged the dupe for now. If somehow this is PowerShell's doing or the module you need to address the likely truncation issue by comparing your columns and the data you are putting into them first. Dupes are not bad. We can reverse it if done in error. – Matt May 30 '17 at 14:33
  • 2
    I found that on my SQL server client I had flag ANSI_WARNINGS off, and powershell had them On. I found what column was taking too much data. Thanks guys. – Simon Yo May 30 '17 at 14:42
  • No problem! Glad we could help. Sometimes it just takes a nudge in the right direction. – Jacob H May 30 '17 at 14:54

0 Answers0