BCP error -Unable to open BCP host data-file

  • 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 !

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

  • 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

  • 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."

  • 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.

  • 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

  • the actual error never made it into the post.

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

  • Check permissions for the account running SQL Server Agent.

    It could be not the same as for SQL Server.

    _____________
    Code for TallyGenerator

  • 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

     

     

     

  • Run

    exec master..xp_fixeddrives

    What is the result?

    _____________
    Code for TallyGenerator

  • 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

  • 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

  • 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."

  • 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

  • specify the name of server

    servername.dbo.T1

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

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

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