none
Unable to open BCP host data-file

    Question

  • I am having trouble with BCP. I get the same error with xp_cmdshell as I do with entering bcp as a DOS command. I have checked and rechecked the file names and permissions and even restart the PC.

    Does BCP even work when SQL Server 2005 Express on a PC?

    declare @sql varchar(8000)
    select @sql = 'bcp SYMITAR..ACCOUNT in C:\test\EXTRACT.ACCOUNT -T -f C:\test\ACCOUNT.FMT -S'+@@servername
    exec SYMITAR..xp_cmdshell @sql
    GO

    Volume in drive C has no label.
    Volume Serial Number is 08E5-2414

    Directory of C:\test

    02/13/2007 08:44 AM <DIR> .
    02/13/2007 08:44 AM <DIR> ..
    08/31/2006 09:11 AM 27,503,161 EXTRACT.ACCOUNT
    08/31/2006 09:12 AM 6,879 FMT.ACCOUNT
    02/07/2007 08:46 AM 220 ACCTTYPE.FMT
    02/13/2007 08:44 AM 0 filelisting.txt
    02/07/2007 08:33 AM 220 ACCTTYPE.xml
    5 File(s) 27,510,480 bytes
    2 Dir(s) 54,344,847,360 bytes free

    Could the fact that it's actually pulling BCP from c:\Program Files\Microsoft SQL Server\80\Tools\binn\ be a problem? I know this because it doesn't recognize the -x extensionof the bcp command.

    PATH:
    %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Common Files\GTK\2.0\bin;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;c:\Program Files\Microsoft SQL Server\90\Tools\binn\;c:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;c:\Program Files\Microsoft SQL Server\90\DTS\Binn\;c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\QuickTime\QTSystem\

    ...

    I added the c:\test to the path and removed c:\Program Files\Microsoft SQL Server\80\Tools\binn\ from the path. While it recognizes the -x extension now it still gets the same error message:

    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
    NULL
    Tuesday, February 13, 2007 5:17 PM

Answers

  • I got this working. It was having a problem with the format file not the "host" data file
    Wednesday, February 14, 2007 11:24 PM

All replies

  • hi,

    did you check the account running the SQLExpress intance has enought NTFS permissions on the referenced path?

    regards

    Wednesday, February 14, 2007 4:21 PM
  • How do you do that?  

    I have no problem creating a file in C:\test\ using BCP queryout
    Wednesday, February 14, 2007 6:31 PM
  • I got this working. It was having a problem with the format file not the "host" data file
    Wednesday, February 14, 2007 11:24 PM
  • Hi,

    I got the same error:

    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

    when trying to import a character data file into a table using a format file

    It turned out that the problem it wasn't anything to do with the data file itself as the error message suggests. Instead it was the format file that was causing issues, but not because its format or contents, but because I had it opened in Excel while attempted to execute the bcp command at the same time  :-)

    So make sure your data-file and format files (if using any) are not opened by other apps before you run the bcp command

    Thx
    Jeem

    • Proposed as answer by ColSanders Saturday, June 01, 2013 5:19 PM
    Thursday, January 14, 2010 12:29 PM
  • what was the problem with format file? Can you please share...bcoz I am also getting the same error...

    Wednesday, January 27, 2010 10:14 AM
  • did you find the solution?
    please let me know ...I am also getting the same error... all my files are closed ... :(
    Wednesday, January 27, 2010 10:14 AM
  • I had this error but then i realized my output file path was wrong (out C:\C:\TEMP\TEST.XLS). Worked when i corrected this.

    Now I'm wondering how to export with the header names. Anybody with an idea?

    • Proposed as answer by Manhut Wednesday, June 23, 2010 9:04 AM
    Tuesday, April 13, 2010 8:37 AM
  • Select the column names and BCP them into a  file (heading.txt)

    Select the data and BCP them into a file (data.txt)

    Execute a copy command to merge the 2 files (copy heading.txt+data.txt results.txt)

    • Proposed as answer by landmaz Friday, January 13, 2012 2:27 PM
    Friday, July 09, 2010 10:37 PM
  • Thanks, I had the same error and after checking your reply, checked my path and found a spelling error.

     

    That fixed it.

     

    Thank You Hilary Ngeno.

     

     


    kasi.alagu@gmail.com
    Tuesday, April 05, 2011 4:30 AM
  • I got this working. It was having a problem with the format file not the "host" data file
    Same thing happened to me. The message is certainly misleading. I had the format file name wrong,  but the data file name was right. Wasn't even looking at the format file name, since based upon the error message, I was only focused on the data file name.
    Monday, March 26, 2012 8:10 PM