Unable to open BCP host data-file SQL Server error

SQL BCP utility can be used to export data from SQL Server database tables to a text file on local folders or network shares. Recently while testing SQL Server BCP command for export data format options, I faced following SQL Server error.

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

The BCP command in my sample case uses a SQL query to fetch data from database table and tries to export data into a text file. The exact location of the text file is identified in the BCP command. If the required write permission on the target file folder is not granted to SQL Server service, the SQL BCP error "Unable to open BCP host data-file" is thrown by the SQL Server engine.

I used the following Transact-SQL query to return a record set of rows from SQL Server sample database AdventureWorks on SQL Server 2012.


DECLARE @cmd varchar(1000)

SET @cmd = 'bcp "SELECT FirstName, LastName FROM AdventureWorks2012.Person.Person" queryout "c:\textfile.txt" -c -UTF8 -T -Slocalhost'

EXEC master..xp_cmdshell @cmd

Code

I pointed the root folder of C drive on the server to write returned dataset into a text file. Since I did not configure any security settings on root drive (or on your case on the target file folder), SQL Server will throw the below BCP error as shown in the screenshot seen on SQL Server Management Studio.

Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file

After I grant the write permission on the related target file folder for SQL Server service user, I could successfully execute the SQL BCP utility and manage to export data to text file on the server.

Here is another example case and how I solved the BCP error step by step on a SQL Server 2017 instance.
In the following sample SQL BCP query, I try to export data from SQL Server database table into a text file using queryout option which indicates a SQL Select query is used for data reading.


declare @SQL varchar(8000)

set @SQL = 'bcp "SELECT top 1 Customer FROM kodyaz.dbo.MyOrders" queryout "C:\Eralper\Orders.txt" -T -n -S ServerName\InstanceName'

exec xp_cmdshell @SQL

Code

The output of the SQL Server xp_cmdshell stored procedure is as follows because I did not make any security / permission configuration on target output folder.

SQL Server Unable to open BCP host data-file error

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

Launch Services tool.
Find the SQL Server service which your SQL Engine is working.
If you have installed multiple SQL Server instances on your server, choose the correct SQL Server service.

SQL Server service

Double click on the service name to display its Properties.
Switch to Account tab and get the account name that the SQL Server instance is running with.
System administrators will grant write permissions to this service account on the target output file folder.

SQL Server service account under Services

Launch Windows Explorer and right click on the target file folder where you want to export SQL Server data using BCP utility.
Since SQL Server service account does not have write permissions on this file folder, SQL users experience the error Unable to open BCP host data-file
Now we will grant write permission to SQL account "MSSQL$KODYAZ2017" that we have identified in previous step.

permissions for SQL Server service account for BCP export