none
BCP -- Insert record error (SQL2005-Window 2003)

    Question

  • Hi All,

    When we to inset record into the tables through BCP we are getting following errors.

    C:\> bcp ACCT.dbo.dtpro  out  C:\test\dtpro_TA.dat -T-n
    C:\>bcp ACCT.dbo.Semiaa out C:\test\Semiaa_TA.dat -T -n

    BCP out works fine only BCP in have  problems.

    Please can you let me know how to resolved this problem.

    ===========
    Exp - 1
    ===========

    C:\> bcp ACCT.dbo.dtpro  in  C:\test\dtpro_TA.dat -T-n

    Starting copy...
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1

    ===========
    Exp - 2
    ===========

    C:\>bcp ACCT.dbo.Semiaa in C:\test\Semiaa_TA.dat -T -n

    Starting copy...
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1

    ============
    Exp-3
    ============

    C:\>bcp ACCT.dbo.SemiH in C:\test\SemiH_TA.dat -T -n

    Starting copy...SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
    SQLState = 42000, NativeError = 7339
    Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for l
    inked server '(null)' returned invalid data for column '[!BulkInsert].Created'.


    BCP copy in failed


     

    Thanks in Advance

     

     

     

    Monday, August 21, 2006 3:22 PM

Answers

  • When you export native there are no record or row delimiters defined. The image type as well as other datatypes like nvarchar(max) do not have fixed length. When you reimport there is now way to tell where fields and records start or end.

    There are two solutions (described in detail below)

     

    • - Use delimiters that do not occur in the data using the \t and \n switches
    • - Edit the format file to specify the size of the image (only works if it is fixed and known)

    The way arround that I've found is to export with delimiters (works even in binary exports!). There is also a microsoft solution that proposes specifying the actual size of the field if the data size of the image is known (like in the case of a bitmap), that way it can still import even without delimeters. This solution requires you to edit the format file (http://support.microsoft.com/default.aspx/kb/271344). 

    BTW. You can export with custom delimiters using the \t  and \n switches with string switches of up to 10 chars. I like to use combinations of tabs and characters that I suspect won't occur in my data.

    Thursday, April 2, 2009 3:40 PM
  • Save file in Ansi format. That solved my problem.

    I used following command

    C:\Users\vikas>bcp DBName.dbo.Table1 in "c:\test.csv" -t, -c -S .\sql12 -U test -P test

    Friday, January 6, 2012 12:31 AM
  • Friday, January 20, 2012 4:33 PM
    Moderator

All replies

  • There are many reasons this error can happen.  It could be, and often is, that the schemas of the databases are incompatible.  Post a reply containing the schemas of these databases if you can.  That would greatly help in diagnosing the problem.

    Jay

     

    Monday, August 21, 2006 5:20 PM
  • C:\>bcp ACCT.dbo.dtpro in C:\test\dtpro_TA.dat -T-n

    Starting copy...
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1
    ******************************************

    table dtpro

     [id] [int] NULL,
     [objectid] [int] NULL,
     [property] [varchar](64) NULL,
     [value] [varchar](255) NULL,
     [uvalue] [nvarchar](510) NULL,
     [lvalue] [image] NULL,
     [version] [int]NULL,

    ********************************************

     

     

    Monday, August 21, 2006 5:51 PM
  • The error message typically indicates some sort of corruption in the file due to invalid lines, invalid terminators etc. Can you try to create a dummy file on a different location and try? If you are using the file that you BCPed out then do that again but to a different location and try.
    Monday, August 21, 2006 6:55 PM
  • The schema seems simple enough.  It seems odd that you can't import into the same table you exported from, since your table doesn't contain fields that would preclude that.

    I would try what Umachandar suggested, and if that doesn't work, try and isolate which records might be causing the problems by using the -F and -L options of bcp to export subsets.

    Monday, August 21, 2006 9:50 PM
  • Were you ever able to get this resolved?

    Jay Kint

     

    Thursday, August 31, 2006 12:24 AM
  • I ran into the same problem.  It appears to have something to do with the -n (native format) switch.  I changed it to -c (character format) and my import worked.

     

    Todd

    Wednesday, December 5, 2007 7:16 PM
  • When you export native there are no record or row delimiters defined. The image type as well as other datatypes like nvarchar(max) do not have fixed length. When you reimport there is now way to tell where fields and records start or end.

    There are two solutions (described in detail below)

     

    • - Use delimiters that do not occur in the data using the \t and \n switches
    • - Edit the format file to specify the size of the image (only works if it is fixed and known)

    The way arround that I've found is to export with delimiters (works even in binary exports!). There is also a microsoft solution that proposes specifying the actual size of the field if the data size of the image is known (like in the case of a bitmap), that way it can still import even without delimeters. This solution requires you to edit the format file (http://support.microsoft.com/default.aspx/kb/271344). 

    BTW. You can export with custom delimiters using the \t  and \n switches with string switches of up to 10 chars. I like to use combinations of tabs and characters that I suspect won't occur in my data.

    Thursday, April 2, 2009 3:40 PM
  • Save file in Ansi format. That solved my problem.

    I used following command

    C:\Users\vikas>bcp DBName.dbo.Table1 in "c:\test.csv" -t, -c -S .\sql12 -U test -P test

    Friday, January 6, 2012 12:31 AM
  • Friday, January 20, 2012 4:33 PM
    Moderator