Format File works with BCP, but fails with BULK Insert

• Brandon Forest

  SSCommitted

  Points: 1977

  More actions

  June 27, 2011 at 1:46 pm

  #238500

  I have a format file as follows:

  8.0

  4

  1 SQLCHAR 0 2 "" 1 RecNo SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 0 2 "" 2 CD_Lien_Type SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 0 16 "" 3 TX_Lien_Descript SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 0 3 "\r" 4 QT_Lapse_Yrs SQL_Latin1_General_CP1_CI_AS

  I can import into the following table using BCP:

  CREATE TABLE [extractLienType] (

  [RecNo] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [CD_Lien_Type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [TX_Lien_Descript] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [QT_Lapse_Yrs] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  ) ON [PRIMARY]

  My BULK INSERT Statement fails with a right truncation error using the same format file and table. Can anyone suggest a fix?

  Thanks!

  Brandon_Forest@sbcglobal.net

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  June 27, 2011 at 4:31 pm

  #1344893

  Brandon Forest (6/27/2011)

  ---

  I have a format file as follows:

  8.0

  4

  1 SQLCHAR 0 2 "" 1 RecNo SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 0 2 "" 2 CD_Lien_Type SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 0 16 "" 3 TX_Lien_Descript SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 0 3 "\r" 4 QT_Lapse_Yrs SQL_Latin1_General_CP1_CI_AS

  I can import into the following table using BCP:

  CREATE TABLE [extractLienType] (

  [RecNo] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [CD_Lien_Type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [TX_Lien_Descript] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [QT_Lapse_Yrs] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  ) ON [PRIMARY]

  My BULK INSERT Statement fails with a right truncation error using the same format file and table. Can anyone suggest a fix?

  Thanks!

  Brandon_Forest@sbcglobal.net

  I can't suggest a fix without seeing the BCP command and the BULK INSERT command. Also, are you saying that a given file fails the BULK INSERT but that exact same given file works with BCP?

  --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)