Bulk insert data conversion error (truncation) for row 1001

• manoj-411629

  SSC Veteran

  Points: 291

  More actions

  February 26, 2007 at 6:41 am

  #103323

  Hi friends,

  I'm getting the below error when executing the Bulk insert utility with the batch size parameter and greek/Japanese characters. Its failing for what ever value i give for the batch siz e parameter.

  Bulk insert data conversion error (truncation) for row 10001

  Below decription gives an idea about the file input to the bulk insert.

  The BULK INSERT was failing for C.Chinese, Greek and Japanese users. The reason is, we are creating a temporary file for BULK INSERT using the default character encoding, which will corrupt all the data which are in some other encoding. Also, the getBytes() method previously used in writeString()

        method will convert the string into bytes in system default encoding scheme. So, modified the code to write the file in UTF-16 encoding  as below         writer = new BufferedWriter(new OutputStreamWriter(bos, "UTF16"))

  so my input file is UTF-16 encoded. Now My bulk insert command has the following parameters set.

        sql.append("BULK INSERT CONCUR..")

             .append(tbl)

             .append(" FROM '")

             .append(getPath())

             .append("' WITH (")

             .append("BATCHSIZE=10000, ")

             .append("DATAFILETYPE='widechar',") 

             .append("FIELDTERMINATOR='")

             .append("|\t~")

             .append("', ")

             .append("ROWTERMINATOR='")

             .append(":\\n")

             .append("', ")

             .append("MAXERRORS=1, ")

             .append("CODEPAGE = 850, ")

             .append("TABLOCK")

             .append(")");

  Can any one suggest me the possible reason for this error.  Have I given the right value for the CODEPAGE parameter? what value should I give for the CODEPAGE to support UTF-16

  Thanks

  Manoj

   

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  February 26, 2007 at 7:29 pm

  #691942

  >>Bulk insert data conversion error (truncation) for row 10001

  That error has nothing to do with the batch size... it has everything to do with the column size of your target table.  The error is saying that if it tries to insert a given field from the file, it would need to truncate it to make it fit the width of a column in the table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• manoj-411629

  SSC Veteran

  Points: 291

  More actions

  February 26, 2007 at 9:24 pm

  #691955

  Jeff,

  Thanks for the quick response. As you mentioned if it is a problem with the column size of the target table then how does the insert going fine with out the the batchsize parameter.

  I tried both without giving the batchsize parameter and batchsize=0. The bulk insert goes fine.

  I think it is not able to split the input file into batches. input file is UTF-16 encoded. what should be the corresponding "codepage" parameter for a UTF-16 encoded input file. I tried codepage 850.

  Thanks

  Manoj

• manoj-411629

  SSC Veteran

  Points: 291

  More actions

  February 28, 2007 at 9:21 pm

  #692496

  Can anyone help me on this please... Its very urgent...