none
SQL - data conversion error

    Question

  • Hi,

    While trying to enter the data "Dëshmorët" in Sql Server Column of datatype varchar(40). I'm receiving the following error:
    Error Description: System.Data.SqlClient.SqlException (0x80131904): Bulk load data conversion error (truncation) for row 4, column 378 (PAX_EO).
    Bulk load data conversion error (truncation) for row 7569, column 378 (PAX_EO).

    When I change the value to "Deshmoret" i.e. english 'e', it works perfectly fine. How to resolve it?

    Thanks.


    Kunal G

    Friday, March 29, 2013 10:18 AM

Answers

  • First of all, try doing it directly in SSMS:

    DECLARE @Sample TABLE 
    	( 
    		Col1 VARCHAR(40),
    		Col2 NVARCHAR(40)
    	);
    
    INSERT INTO @Sample 
    VALUES 
    	( '"Dëshmorët"', '"Dëshmorët"' ),
    	( '"Deshmoret"', '"Deshmoret"' ),
    	( N'"Dëshmorët"', N'"Dëshmorët"' );
    
    SELECT	*
    FROM	@Sample;

    Using a default installation should work. When this is the case, then the problem comes from your application..

    • Proposed as answer by Aalamjeet Rangi Monday, April 01, 2013 7:47 AM
    • Marked as answer by Kunal G Friday, April 05, 2013 6:26 AM
    Friday, March 29, 2013 2:19 PM

All replies

  • First of all, try doing it directly in SSMS:

    DECLARE @Sample TABLE 
    	( 
    		Col1 VARCHAR(40),
    		Col2 NVARCHAR(40)
    	);
    
    INSERT INTO @Sample 
    VALUES 
    	( '"Dëshmorët"', '"Dëshmorët"' ),
    	( '"Deshmoret"', '"Deshmoret"' ),
    	( N'"Dëshmorët"', N'"Dëshmorët"' );
    
    SELECT	*
    FROM	@Sample;

    Using a default installation should work. When this is the case, then the problem comes from your application..

    • Proposed as answer by Aalamjeet Rangi Monday, April 01, 2013 7:47 AM
    • Marked as answer by Kunal G Friday, April 05, 2013 6:26 AM
    Friday, March 29, 2013 2:19 PM
  • Can you elaborate on exactly how you are performing the bulk insert?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, March 29, 2013 3:28 PM
    Moderator
  • Thanks, @Sample script is working properly. Any pointers what should I try?

    Kunal G

    Monday, April 01, 2013 6:13 AM
  • The problem is that we don't know what you're exactly doing. Especially what kind of application do you use? The error message mentions a bulk load operation. This indicates that maybe one destination column is defined as VARCHAR instead of NVARCHAR.
    Monday, April 01, 2013 10:46 AM
  • Hello,

    Just a little add-in to the excellent post for Stefan.

     "Dëshmorët" contains letters  (  e with diaeresis ) which can be accepted by VARCHAR , only by NVARCHAR ( Unicode letter )

    e with diaeresis is used for example in French  ( it needs an AZERTY keyboard ) , it is unknown in English ( which needs a QWERTY keyboard ). This letter exists in the german language ( maybe in some other languages all needing an Unicode representation for the letters )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, April 01, 2013 9:39 PM
    Moderator
  • The problem is that we don't know what you're exactly doing. Especially what kind of application do you use? The error message mentions a bulk load operation. This indicates that maybe one destination column is defined as VARCHAR instead of NVARCHAR.
    I'm using BULK INSERT to insert the data :
    EXEC('BULK INSERT ' + @LocalTableName + ' FROM ' + '''' + @DataFileLocation + '''' + ' WITH (FORMATFILE =  ' + '''' + @FormatFileLocation + '''' + ' )')

    The column where Dëshmorët will be inserted is "PA6_LOT_2", it's entry in Formal File.
    335     SQLCHAR             0       40      ""     335   PA6_LOT_2                                               SQL_Latin1_General_CP1_CI_AS

    I have also tried changing above to SQL_Latin1_General_CP1_CI_AI, even then it doesn't work. I have tried changing PA6_LOT_2 to NVARCHAR(MAX) and also NVARCHAR(40) in SQL, in both cases it gives error.

    In addition to above, even though the column name is PA6_LOT_2, in error it shows column PAX_EO. Entry of PAX_EO in Formal File.
    378     SQLCHAR             0       1       "\r\n"   378   PAX_EO                                                 SQL_Latin1_General_CP1_CI_AS

    Please let me know if you require some other details?



    Kunal G


    • Edited by Kunal G Tuesday, April 02, 2013 7:00 AM
    Tuesday, April 02, 2013 6:23 AM
  • Perhaps the file is UTF8 encoded.  In that case, characters outside the 0-127 are stored as multiple bytes.  Try converting the file to UTF 16 (e.g. open the file in notepad and save as Unicode) and specify DATAFILEFORMAT = 'widechar' on the BULK INSERT statement.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, April 02, 2013 12:40 PM
    Moderator
  • I have tried converting the file to UNICODE as you have suggested, still the same error.

    Kunal G


    • Edited by Kunal G Wednesday, April 03, 2013 10:59 AM
    Tuesday, April 02, 2013 2:46 PM