BCP error -Unable to open BCP host data-file

Question

Post reply

BCP error -Unable to open BCP host data-file

Sapna Ranka

SSC Eights!

Points: 810
More actions
November 15, 2005 at 9:19 am

#90817

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

I am getitng the above error when i am executinf BCP. The file location is not on the local machine. it is a network drive. Please help !

Viewing 15 posts - 1 through 15 (of 45 total)

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

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

You can map the network drive first and then use that mapped drive to do it.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Whenever I get this message, it's because of one of three things:

1) The path/filename is incorrect (check your typing / spelling)

2) The file does not exist. (make sure the file is where you expect it to be)

3) The file is already open by some other app. (close the other app to release the file)

For 1) and 2) - remember that paths are relative to where bcp is executing. Make sure that bcp.exe can access the file/path from it's context.

/Kenneth

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Also, if there are spaces in the name don't forget the double quotes ... like "C:\Program Files" ... If you're executing from xp_cmdshell you might try a DIR command in xp_cmdshel to check things out security-wise. I'd also stay away from mapped drives since SQL Server is only made aware of them each time the service is started - UNCs are the way to go.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Sapna Ranka SSC Eights! Points: 810 More actions · Answer 4

Thanks All for you reply. This issue was resloved. i was trying to BCP out on a logical drive which was mapped correctly. but then my network admin told me that sql server could not access the network drive due to some permission issues.

Patrick Nguyen SSC Enthusiast Points: 127 More actions · Answer 5

i got this error while running a bcp command from the xp_cmdshell

if i run the bcp separately on the dos prompt, it runs fine

Anyone knows the info to correct this problem? Thanks

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

the actual error never made it into the post.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Check permissions for the account running SQL Server Agent.

It could be not the same as for SQL Server.

_____________
Code for TallyGenerator

Sankar-403950 SSC-Addicted Points: 420 More actions · Answer 8

Hi,

I am getting the same problem in the following scenario

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

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Run

exec master..xp_fixeddrives

What is the result?

_____________
Code for TallyGenerator

Sankar-403950 SSC-Addicted Points: 420 More actions · Answer 10

Hi,

Thanks for quick reply, below is the result

C 9308

D 31946

Pls let me know if you need any other information

Thanks,

Sankar

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

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

.. is this the actual commandline? It looks a bit fishy in two places.

Unless you want your file to be named 'Archive', you haven't supplied a filename, only the directory 'D:\Archive'

..is the delimiter a pipe + semicolon?

In any case, a pipe on a commandline acts as a redirection operator, so try enlosing it in quotes.

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

(if the semicolon is just some sort of 'statement-end', just remove it-)

/Kenneth

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 12

One more point. The table identified as T1 is not qualified. Depending on the user ID supplied this may change what database the table will be looked for in. By default it will be the master database. My suggestion is to qualify the table name on your bcp statement in order to eliminate any potential confusion or errors.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 13

This can also be caused by the account BCP is using, since you are using SQL Login, this will be either:

Your account if excuted from Command line

The SQL Server service account if using xp_cmdshell

SQL Server Agent service account, if it is being executed in a CmdExec or T-SQL with xp_cmdshell step. The account can be configured within the Job as well.

The BCP account needs access to the file, for UNC this includes the share.

A trick for Local Service is to grant the SQL Server Computer account these network permissions.

For UNC with the service using a Domain account, the permissions should be verified by logging in as the service account, browse to the file in question, and run BCP from the cmd line.

Andy

fasttrack SSCertifiable Points: 6071 More actions · Answer 14

specify the name of server

servername.dbo.T1

otherwise bcp (executed on cmd line) does not where is T1