BCP error -Unable to open BCP host data-file

Question

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

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

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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

James.