11

I've just create a new table in my sqlserver name exporttable now I'm trying to push out using cmd bcp but om getting this following error:

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

Here is my path:

C:\Users\Serge>BCP Testing.bdo.Exporttable out "C:\Users\Serge\Desktop" -C -T

anyone can help ?

After trying Shnugos suggestion to add a filename I got this error:

SQLState = S0002, NativeError = 208 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'Testing.bdo.ExportTable'. SQLState = 37000, NativeError = 11529 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these. –

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
MK55SDV
  • 137
  • 1
  • 2
  • 7
  • Have you tried googling that error message? Please show some research effort. People are willing to help you if you at least try to solve the problem at your own. Please read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) and [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask). Then, update and *improve your question*. – Martin Nyolt Sep 13 '16 at 08:25

4 Answers4

9

From the error I take, that the data file cannot be opened:

C:\Users\Serge>BCP Testing.bdo.Exporttable out "C:\Users\Serge\Desktop\MyFile.txt" -C -T

I think, you have to add a filename behind the \Desktop. Desktop is an existing directory and cannot be opened as file ...

And - btw - it might be necessary to add -S Servername...

UPDATE

Found this here

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

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • i've done that and i'm getting this errror SQLState = S0002, NativeError = 208 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'Testing.bdo.ExportTable'. SQLState = 37000, NativeError = 11529 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these. – MK55SDV Sep 13 '16 at 08:29
  • Ok i Got it but i got one more question to ask you i manage to figure out but i'm getting this issue A valid table name is required for in, out, or format options. – MK55SDV Sep 13 '16 at 09:15
  • 1
    @serge, I just see `Testing.bdo.Exporttable` in you command. Is this just a simple typo and you wanted to write `dbo`? – Shnugo Sep 13 '16 at 09:17
  • Really i'm sorry But i Still get the issue BCP Serge-st-11.Testing.dbo.ExportTable OUT "D:\Work\Sept.2016\"-C -T i still get the same issue sorry I'm not so good with bcp.. i will be greatefull to get that sort it – MK55SDV Sep 13 '16 at 09:22
  • @serge, Your path - again - has not got a file name... Use BCP with 3-part identifier and put the Server's name at the end using `-S ServerName`... – Shnugo Sep 13 '16 at 09:25
  • 1
    It was indeed the path for me, I was under the impression that bcp would create the missing folder for me. Thanks! – refex May 29 '20 at 09:34
7

If you are running BCP through xp_cmdshell, run the following--> xp_cmdshell 'whoami'; GO --Make sure whatever user value you get back has full access to the file in question

Josh McCoy
  • 91
  • 1
  • 2
  • This suggestion fixed my problem with the error `Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file` – user890332 Jun 16 '20 at 19:10
  • I think, this would not work if you are accessing the SQL Server remotely (for example, Azure SQL Db etc.). Also see the post from @user5818738 above. – nam Apr 04 '22 at 21:22
5

Run: EXEC master..xp_cmdshell 'DIR C:\Users\Serge\Desktop', this will show if you have access to the path.

Remember if you are accessing SQL remotely or over a network, the output ie. "C:\Users\Serge\Desktop" will be the C drive on the SQL Server, not your remote PC you are working on.

user5818738
  • 61
  • 1
  • 1
3

I know this is old, but you also appear to have the schema spelled wrong. C:\Users\Serge>BCP Testing.bdo.Exporttable out "C:\Users\Serge\Desktop" -C -T

s/b

C:\Users\Serge>BCP Testing.dbo.Exporttable out "C:\Users\Serge\Desktop" -C -T