SQL Bulk copy (unicode)

Question

i have a problem with bulk load into my database. Server throws error "String or binary data would be truncated", but my data seems to be ok.

Im using UTF-16LE data file with XML format file. Thanks for your answers

Here's my format file:

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NCharTerm" TERMINATOR=";\0" />
  <FIELD ID="2" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharTerm" TERMINATOR=";\0" />
  <FIELD ID="6" xsi:type="NCharTerm" TERMINATOR=";\0" />
  <FIELD ID="7" xsi:type="NCharTerm" TERMINATOR=";\0" />
  <FIELD ID="8" xsi:type="NCharTerm" TERMINATOR=";\0" />
  <FIELD ID="9" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="11" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="12" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="13" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="14" xsi:type="NCharTerm" TERMINATOR=";\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="15" xsi:type="NCharTerm" TERMINATOR="\n\0" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="RefundId" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Date" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="VariableSymbol" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="RefundType" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="TollAmount" xsi:type="SQLNUMERIC" PRECISION="28" SCALE="12"/>
   <COLUMN SOURCE="6" NAME="DepositAmount" xsi:type="SQLNUMERIC" PRECISION="28" SCALE="12"/>
   <COLUMN SOURCE="7" NAME="OperativeAmount" xsi:type="SQLNUMERIC" PRECISION="28" SCALE="12"/>
   <COLUMN SOURCE="8" NAME="OIAmount" xsi:type="SQLNUMERIC" PRECISION="28" SCALE="12"/>
  <COLUMN SOURCE="9" NAME="Currency" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="Prefix" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="BankAccountNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="BankCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="BankIBANCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="BankSwiftCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="15" NAME="BeneficiaryName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Here's my data file:

301;2008/05/05;444441;CN;50;1550;70;900;CZK;;8888888888;0800;;;
302;2008/05/06;444442;UP;50;1550;70;900;CZK;;8888888888;1234;;;
303;2008/05/07;444443;OP;50;1550;70;900;CZK;;8888888888;;;;
304;2008/05/08;444444;CN;60;1550;70;900;CZK;;;0400;;;
305;2008/05/09;444445;UP;70;1550;70;900;CZK;;1234567891;0800;;;
306;2008/05/10;444446;OP;80;1550;70;900;CZK;;8888888888;0800;;;
307;2008/05/11;444447;CN;90;1550;70;900;CZK;;8888888888;0800;CZ7308000000008888888888;;
308;2008/05/12;444448;UP;100;1550;70;900;CZK;;8888888888;0800;;12345678901;
309;2008/05/13;444449;OP;110;1550;70;900;CZK;;;;12234567890qqqq;COBADEFF;**123456789012345678901234567890qqqq**
310;2008/05/14;444450;CN;120;1550;70;900;CZK;;;;;COBADEFF;Herr Norbert Ramscheid
312;2008/05/16;444452;OP;140;1550;70;900;CZK;;;;RO49AAAA1B31007593840000;RNCBROBU;Mr. Papalescu
313;2008/05/17;444453;CN;150;1550;70;900;CZK;;678907890;;;RNCBROBU;Mojmir Fagaras
314;2008/05/18;444454;UP;160;1550;70;900;CZK;;;;;RNCBROBU;Mr. Without Bank Account
315;2008/05/19;444455;OP;170;1550;70;900;CZK;;;;RO49AAAA1B31007593840000;;NoSWIFT Bill
316;2008/05/20;444456;CN;180;2000;70;900;CZK;;;;RO49AAAA1B31007593840000;RNCBROBU;
317;2008/05/21;444457;UP;190;1550;70;900;CZK;;;;DE89370400440532013000;COBADEFF;

score 0 · Answer 1 · answered Aug 13 '13 at 14:30

I assume there could be a problem with a destination table where the data should be loaded into.

Example
E.g. when you are loading data in string of 10 characters into the column which is e.g. varchar(6) data type, the data could not be loaded into this column as it supports only 6 characters and you would get the error as you are getting now - the data would be truncated.

SQL Bulk copy (unicode)

1 Answers1