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