none
#@ Row 1, Column 7: String data, right truncation @#

    질문

  • I am trying to BCP a ton of data files into a SQL 2005 database.  The first row of data in one of my files looks like:

    1000|100000156752|100000176409|100000000000|100000000000|9.4|M|9.4||1/22/1993||1||||100|||||||||1|1/22/1993|||||||

    The error file has this:

    #@ Row 1, Column 7: String data, right truncation @#
    0    0    0    0    0    .00    17|27.7|M|27.7||2/2/1993||1||||100|||||||||1|2/2/1993|||||||

    I've built a format file based on the SQL Table definitions with this command:
    FOR %%f IN (*.*) DO bcp IRIS.dbo.%%f format nul -T -n -t"|" -r"\n" -f%%f.format

    The format file for lines 1-8 look like:
    1       SQLNUMERIC    1       19      "|"                       1     SITE_ID                         ""
    2       SQLNUMERIC    1       19      "|"                       2     WEL_ID                          ""
    3       SQLNUMERIC    1       19      "|"                       3     WPOO_ID                         ""
    4       SQLNUMERIC    1       19      "|"                       4     WSMP_ID                         ""
    5       SQLNUMERIC    1       19      "|"                       5     CC_ID                           ""
    6       SQLNUMERIC    1       19      "|"                       6     CORE_LENGTH                     ""
    7       SQLCHAR       2       2       "|"                       7     LENGTH_MEASM_UNIT_ID            SQL_Latin1_General_CP1_CI_AS
    8       SQLNUMERIC    1       19      "|"                       8     LENGTH_OF_CORE_RECOVD           ""

    Here is the DOS command window results for this file:
    F:\Data>cd import

    F:\Data\Import>FOR %f IN (CONVT_CORES.*) DO bcp IRIS.dbo.%f in %f -e..\BCP_Error
    \%f.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\%f.format

    F:\Data\Import>bcp IRIS.dbo.CONVT_CORES in CONVT_CORES -e..\BCP_Error\CONVT_CORE
    S.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\CONVT_CORES.format

    Starting copy...
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22003, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

    BCP copy in failed

    F:\Data\Import>cd..


    So, what is wrong???
    2006년 11월 9일 목요일 오후 6:10

답변

모든 응답

  • Seems, that the data types do not make, make sure that the data types in the destination tables are capable to import the data, otherwsie truncation will take place and will chop the data.

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    2006년 11월 11일 토요일 오전 10:43
  • All datat types are made larger than needed by the largest data piece being imported.  It works quite easily in SSIS but BCP gives me this error.  With a few hundred tables, SSIS will take me days to setup and execute each one.

    Something is wrong with BCP in SQL 2005!!!
    2006년 11월 14일 화요일 오전 12:16
  • Hi Guys,

    Make sure the table schema on the target server matches the one on the source server. This will solve the proble. There is nothing wrong  in the BCP in SQL 2005.

    Roshan.

     

    2006년 12월 6일 수요일 오전 3:28
  • Try using sqlchar across the board.  If you are exporting from a SQL server and then importing the data back in, use native format to export, and specify this in  your format file.

    I ran into this problem myself a few weeks ago.

    2006년 12월 6일 수요일 오후 2:47
  • Exporting from Oracle.  I've even gone so far as to make all columns varchar(xx) to see if that would fix it.  It does *not* fix it.  I've tried various formats for the CSV and they all give me this error.  I exported to XML, wrote a small app to import that and I have problems with one, and only one, table out of 736 tables.  I cannot say BCP works as I cannot get it to work.  SSIS works on all but 7 tables so I know the data can go in.  <bah type="humbug" />
    2006년 12월 6일 수요일 오후 5:49
  • I have bcp jobs that run every night from production to staging, and one started failing after putting SP2 on SQL server. It was only on one table. I finally fixed the problem by deleting the table and recreating it from a script extracted from production. All I can figure is that something in the table schema got corrupted.
    2007년 5월 14일 월요일 오후 3:51
  • I had this problem too. You probably made the same mistake. The field is of type decimal. If you make a field of type decimal(10) you think that means 10 chars. No. It means 10 digits, all to the left of the decimal place. If you have any numbers like 2.75 the digits to the right of the decimal place will be lopped off and SQL Server will scold you. You think that number was much less than 10 characters wide. Too bad. SQL Server assumes you mean 0 digits ro right of decimal if you do not tell it otherwise. As soon as I fixed to decimal (10,3) all was happy again.

    2012년 7월 4일 수요일 오후 11:22
  • All datat types are made larger than needed by the largest data piece being imported.  It works quite easily in SSIS but BCP gives me this error.  With a few hundred tables, SSIS will take me days to setup and execute each one.

    Can you explain why is that? You can process entire folder of files  (foreach...) with one SSIS package.

    SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    2012년 7월 19일 목요일 오전 8:52