Post reply

bcp fails

  • June 24, 2004 at 2:49 pm

    #180798

    Hi,

    I have a weird problem with bcp. Below is my bcp command

    master..xp_cmdshell 'BCP Mydb.dbo.tb_user in D:\test.dat -b10000 -fD:\test.fmt -SDevbox -Utest_user -Ptest_user'

    This was earlier working on SQL 6.5 box. Now, am doing the same operation on 2000 after migration. But I get the following error

    Starting copy...

    SQLState = 22001, NativeError = 0

    Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

    NULL

    0 rows copied.

    But I was told that this works fine in the earlier SQL 6.5 setup.

    I see from the format file, the column delimiter is "" and row delimiter is \r\n.

    Am I missing something here ? Also, I tried using bcp for another data file and I get the following error

    SQLState = S1002, NativeError = 0

    Error = [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

    Do this an issue with the ODBC ? Please let me know if anybody has faced this issues, as its very critical.

    Thanks in advance.

  • June 25, 2004 at 3:17 am

    #511918

    Hallo!

    >>Error = [Microsoft][ODBC SQL Server Driver]>>String data, right truncation

    SQL Server tells you that your table contains a string field that have lesser length than in your format file.

    Check your table and format file. Correct one or another.

    Good luck!

     

  • June 25, 2004 at 6:14 am

    #511934

    Hi,

    I did check that. Its the same. The dat file has a new line character as rowdelimiter. The bcp is unable to read that. I have 25 columns in the table. And I get an error while inserting the 25 th column in the table. It is trying to insert the new line and carriage return as well along with the data of 25th column. That column is defined as char(1) in the table.

    Any pointers like how to fix this ?

    Many thanks.

     

     

     

  • June 25, 2004 at 7:34 am

    #511950

    Have you tried the -F flag to indicate the number of rows to copy into your table?

    Duncan

  • June 25, 2004 at 9:54 am

    #511990

    Use -c flag and see. It also takes \t as tabs and \n as row terminators. 

    master..xp_cmdshell 'BCP Mydb.dbo.tb_user in D:\test.dat -b10000 -c -fD:\test.fmt -SDevbox -Utest_user -Ptest_user'

    Hope this helps!!!

     

  • June 25, 2004 at 2:41 pm

    #512045

    Can u show ur format file once?

  • June 25, 2004 at 3:54 pm

    #512050

    Looks like its an issue with the data file.

    I tried all the options but this works with -n option.

    There are some extra characters which I after truncating, the bcp works fine.

    Trying to figure out, what the datafiletype for this

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply