none
String or binary data would be truncated RRS feed

  • Question

  • I get an error when trying to insert this statement in a varchar field.

    Msg 8152, Level 16, State 4, Line 1
    String or binary data would be truncated.

    UPDATE WTR1 SET WTR1.U_ClientDesc = ('From: CINE MAISON ROYALE(CMR)'
    + CHAR(10) + CHAR(13) +
    'VENDOR CONTACT :Roberto or Max 514-459-1021'
    + CHAR(10) + CHAR(13) +
    'DO NOT TOP LOAD-TOP FRIEGHT'
    + CHAR(10) + CHAR(13) +
    'NE PAS EMPILER - CHARGER PAR LE HAUT SEULEMENT'
    + CHAR(10) + CHAR(13) +
    '1 SKID-24x36x50. Total # 250 Lbs'
    + CHAR(10) + CHAR(13) +
    ''
    + CHAR(10) + CHAR(13) +
    ''
    + CHAR(10) + CHAR(13) +
    '_______________ __1___  __________________'
    + CHAR(10) + CHAR(13) +
    'CMR                             SKID      TRANSPORT')
    FROM OWTR INNER JOIN WTR1 ON OWTR.DocEntry = WTR1.DocEntry
    WHERE OWTR.DocDate = '2020-01-23' AND FATHERCARD = 'GTHEAD'
    AND WTR1.ItemCode = '624262301042' AND wtr1.Quantity = '2'

    Monday, January 20, 2020 3:37 PM

All replies

  • The maximum length of the column WTR1.U_ClientDesc might be less than the length of data you want to update.

    A Fan of SSIS, SSRS and SSAS

    Monday, January 20, 2020 3:40 PM
  • If you use SQL Server 2016 SP2 CU6+ you can add  a trace flag OPTION (QUERYTRACEON 460) to see exactly what is a problematic string

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 21, 2020 5:36 AM
    Answerer
  • Hi  jbrotto,

    The correct way to fix the issue is to find the column causing error and correct the data or column length.

    In your example , please check the column 'U_ClientDesc '. The length of 'U_ClientDesc ' which you will update is too long. Please check .

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 21, 2020 7:07 AM
  • When entering the value in our ERP manually it fits. Could it be a special character causing the problem?
    Wednesday, January 22, 2020 1:09 AM
  • When entering the value in our ERP manually it fits. Could it be a special character causing the problem?
    declare @s varchar(max)='From: CINE MAISON ROYALE(CMR)'
    + CHAR(10) + CHAR(13) +
    'VENDOR CONTACT :Roberto or Max 514-459-1021'
    + CHAR(10) + CHAR(13) +
    'DO NOT TOP LOAD-TOP FRIEGHT'
    + CHAR(10) + CHAR(13) +
    'NE PAS EMPILER - CHARGER PAR LE HAUT SEULEMENT'
    + CHAR(10) + CHAR(13) +
    '1 SKID-24x36x50. Total # 250 Lbs'
    + CHAR(10) + CHAR(13) +
    ''
    + CHAR(10) + CHAR(13) +
    ''
    + CHAR(10) + CHAR(13) +
    '_______________ __1___  __________________'
    + CHAR(10) + CHAR(13) +
    'CMR                             SKID      TRANSPORT' 

    select len(@s)
    --286

    What is your column length?
    Wednesday, January 22, 2020 1:16 AM
    Moderator