Post reply

BCPing data from SQL2000 to SQL7 causes error.

  • June 18, 2004 at 3:05 am

    #82997

    Hi All,

    I'm trying to transfer data from SQL2000 to SQL7 using bcp. Currently I can't even get a test table with 1 row of data to work. The error I'm getting is "Unexpected EOF in BCP data-file."

    I created the following table on the SQL2000 database:

    create table Duncan_test.dbo.testtable

    (name char(30), address char(100), nameid int)

    I then added one line of data with the following values; 'Duncan', 'My House', 1.

    I backed up the data using the following bcp command:

    bcp "Duncan_test.dbo.testtable" out "c:\temp\testdata.dat"

         -n -V70 -Uusername -Ppassword /Sservername

    I can load the data back into SQL2000 but when I try to load it into SQL7 I get the EOF error. The bcp load statement for the SQL7 box is as follows:

    bcp "duncan_test.dbo.TestTable" in "c:\temp\testdata.dat" -n -Uusername -Ppassword /Sservername

    As I'm running out of hair to pull out can anyone shed any light as to why I might be getting the error? The destination table does exist on the SQL7 server.

    Kind regards,

    Duncan

  • June 21, 2004 at 6:28 am

    #511196

    Duncan,

    I'm sorry to say I tried everything you did, substituting tempdb for Duncan_test , and it worked fine.

    Are there any special attributes/options associated with the SQL2000 version of Duncan_Test that could be affecting it?

    Is the table defined identically on SQL7?


    Cheers,
    - Mark

  • June 21, 2004 at 11:12 pm

    #511339

    If you need to try something, you could add -c to both bcp commands, to make ascii data files. My suspects are Unicode or Collation differences.

    In further desperation you could create format files - do a

    bcp table format -f format.fmt -c

    from both databases and compare the format files, to come up with a format file for both out and in bcps.

  • June 22, 2004 at 4:54 am

    #511379

    Thank you both for your assistance. I have now got the process working with the -n switch.

    I think part of the problem was that I was also trying to call the bcp commands from within a Visual FoxPro application using the xp_cmdshell stored prodedure. Once I worked out the commands to run from the command line and verified they worked in SQL Query Analyser the rest was (fairly) straightforward.

    Thanks once again.

    Duncan

Viewing 4 posts - 1 through 3 (of 3 total)

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