Problem with Bulk Insert.

• martin-210793

  Ten Centuries

  Points: 1083

  More actions

  December 16, 2010 at 5:04 am

  #388447

  Hi, I've got the following raw data:

  C100071540 1 10120101122 040039

  using the following format file:

  <?xml version="1.0"?>

  <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="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="15" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

  </RECORD>

  <ROW>

  <COLUMN SOURCE="1" NAME="InvoiceNumber" xsi:type="SQLNVARCHAR"/>

  <COLUMN SOURCE="2" NAME="RowCount" xsi:type="SQLNVARCHAR"/>

  <COLUMN SOURCE="3" NAME="AmountSum" xsi:type="SQLNVARCHAR"/>

  <COLUMN SOURCE="4" NAME="ExtractDateTime" xsi:type="SQLNVARCHAR"/>

  </ROW>

  </BCPFORMAT>

  But I keep getting the following error:

  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (ExtractDateTime).

  Anybody got any ideas on what I'm doing wrong. I know its because of the space between the datetime but how do I get over this?

  Thanks and Regards,

  Martin

• martin-210793

  Ten Centuries

  Points: 1083

  More actions

  December 16, 2010 at 6:23 am

  #1263410

  Thanks, got it sorted. My program which was uploading these was using the wrong table mapping...