SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2016
download SQL Server 2014
download SQL Server 2012

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.

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.

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems