BCP error -Unable to open BCP host data-file

• Sankar-403950

  SSC-Addicted

  Points: 420

  More actions

  May 8, 2007 at 11:31 pm

  #173585

   

  Hi,

  I have created one table "T1" using filegroup "A" contains only data file "A_1.ndf" with size 100M and disaled autogrowth. This file group size reaches 100M means its 100% full. Now I am trying to archive this "T1" table data using bcp command as follows:

  bcp T1 out D:\Archive -S <Server_Name> -U <username> -P <pwd> -b 10000 -c -t |;

  it throws the below error:

  "SQLState = S1000, NativeError = 0

  Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"

  While executing bcp command, does it requires some space in filegroup "A"?

  Can any body help how to resolve this?

  Thanks

  Sankar

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 9, 2007 at 9:27 am

  #705266

  Not sure since I didn't test the theory but have you made sure that the output file "D:\Archive" can be created by the SQLServer account on the local SERVER drive (remember if you run that from a client machine, the file is created on the server).  Also recommend you always give a file extension to the output file, and make sure D:\Archive is not an existing directory.

   

  James.

• Sankar-403950

  SSC-Addicted

  Points: 420

  More actions

  May 17, 2007 at 4:41 am

  #707057

  When I try to run "bcp" command through procedure with EXEC master..xp_cmdshell, I am getting this error, whereas if I run the “bcp” command itself in command mode (without EXEC master..xp_cmdshell), I do not get any error

  Any ideas?

   

  Sankar

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 17, 2007 at 6:02 am

  #707088

  It still goes back to the basics.  When running the BCP command from the command line are you on your workstation or the server?  the xp_cmdshell is going to run ON the server and with the permissions of the SQL Server start up account.  Just as a test reset the permissions on the directory where the output file will be written to Read/Write EVERYONE.  Also remember the the "D" drive when running BCP in command mode on your workstation refers to your workstation, and "D" drive when running xp_cmdshell refers to the server.

• Sankar-403950

  SSC-Addicted

  Points: 420

  More actions

  May 17, 2007 at 11:51 pm

  #707311

  Hi,

  Thanks for the reply!

  I am doing all these testing activities on SQL Server 2005 machine only,  not from any work station.

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 6:00 am

  #707391

  Did you open the permissions on the output file location?  Just trying to eliminate the obvious first.

  James.