Facing error with BCP utility?

Question

I am getting the following error:

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

while using this bcp utility

bcp "select * from database1.dbo.table1" queryout "c:\result.txt" -c -S10.10.10.100\dev -Usa -P123456 -t!

Please check.

Answer 1

I have gotten that error before when my output file was not able to be accessed. Make sure that you can write to C:\, or the file does not already exist.

Answer 2

Are you running this from the command prompt? I used to get the same error. To resolve it, I would browse to the directory i wanted, in this case "C:\" and I would call:

bcp "select * from database1.dbo.table1" queryout "result.txt" -c -S10.10.10.100\dev -Usa -P123456 -t! 

Notice I removed the "C:\". I am not sure why this works but this removed the error for me.

Answer 3

1. You do not need use -T option (trust login ...) when you use -U and -P
2. Start xp_cmdshell from SQL Server Surface Area Configuration