BCP - String data, right truncation!

• steven.whatmore

  Ten Centuries

  Points: 1048

  More actions

  January 7, 2009 at 10:36 am

  #198070

  Good afternoon,

  We are in the porting an application backed on Sybase to 2K5 are having problems with the BCP of data into the DB.

  When Sybase encounters a field that will not fit into the target column, it merrily truncates the data to fit into the column (which is what we wanted).

  However, when the 2K5 BCP command hits the same data, it does not copy the truncated contents into the target column and provides us with the following error:

  SQLState = 22001, NativeError = 0

  Error = [Microsoft][SQL Native Client]String data, right truncation

  Is there any way to tell the BCP command to truncate the data and slam it into the column, thus ignoring the above error.

  Whatty

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 7, 2009 at 1:15 pm

  #922865

  That "feature" can only be controlled with SET ANSI_WARNINGS OFF which is a connection property. You could try BULK INSERT instead and run it with that SET option "off". I don't think bcp allows you to specify set options except for QUOTED_IDENTIFIERS.

  
  * Noel

• steven.whatmore

  Ten Centuries

  Points: 1048

  More actions

  January 7, 2009 at 1:18 pm

  #922868

  Thanks for the assistance.

  I have actually gone back to my data files and truncated the data to fit in the target columns.

  Luckily the field in question is consistent across all my data files and thus it was not that much of an effort.

  Whatty

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 7, 2009 at 1:19 pm

  #922869

  That's much better (consistent) and does not requires of any tricks!

  
  * Noel