BCPError – Unable to open BCP host data-file

Troubleshooting bcp error : SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

As we already know, how to use bcp to export data from sql server. BCP is largely known command for importing data in sql server as well as exporting data from sql server. Here you will see by using bcp command, we will import  data in sql server table. you can execute the bcp command either on command prompt or using xp_cmdshell in stored procedure.

If you don’t have access permission to file then it  will throw error stating unable to open host file as shown below.

We are using command prompt to execute bcp statement.

bcp northwind.dbo.ImportData_Test in ‘D:\ImportData\custdb.txt’ -T -S.

If you don’t have access permission to file then it  will throw error stating unable to open host file as shown below.

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

In my case, sql server service account didn’t had permission to access file.

Workaround:

You can also use bulk insert statement to import or insert bulk data directly into table

BULK INSERT dbo.ImportData_Test FROM ‘C:\importdata\custdb.txt’

WITH ( FIELDTERMINATOR =’,’, ROWTERMINATOR =’\n’)

You can verify the data by using select statement on importdata_test table.

Leave a Reply

  

  

  


9 − 7 =