How to Open Bcp Host Data-File

Unable to open BCP host data-file

First, rule out an xp_cmdshell issue by doing a simple 'dir c:*.*';

Check out my blog on using BCP to export files.

I had problems on my system in which I could not find the path to BCP.EXE.

Either change the PATH variable of hard code it.

Example below works with Adventure Works.

-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO

Before changing the path to \110\ for SQL Server 2012 and the name of the database to [AdventureWorks2012], I received the following error.

After making the changes, the code works fine from SSMS. The service is running under NT AUTHORITY\Local Service. The SQL Server Agent is disabled. The output file was created.

SQL Server Unable to open BCP host data-file

Went to a friend who is an expert. The issue was I need to provide permissions to the serivce account from which SQL server was trying to execute xp_cmdshell.

The challenge was the "Service" account was named something different when looking at the "Log On" account that was associated with the SQL Server Service running. That had a NT account associated with the default instance name. What you wanted to select was the account named "SERVICE".

Also, the old machine seemed to work without the need to add this account to that permission.

Anyway, it works now.

Thanks for your help and interest.

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

It is not your account that needs access to the share. Since you are running the BCP command through the "xp_cmdshell" command, the account that actually executes the bcp command is the same account that is running the SQL Server service on the SQL Server box. When you use "xp_cmdshell" you leave your session/authentication behind and pass control to a new session outside of SQL Server. This is done using the account that is running the SQL Server service and the command is executed on the OS that the SQL Server is running on. Most likely, you are not even able to logon to the OS underneath the SQL Server.

You must confirm that the SQL service account has access to the share.

Im not certain that this is your issue, but it may be. You can test other possibilties by:

1. Print the contents of the @SQL command instead of executing it. Copy that value into a command window and try to run the command yourself. This will test that the command is valid and that things like paths are valid.

2. If you can, log onto the physical server (windows?) that your SQL Server is running on. Log on as the SQL Server service account. Then try to execute the bcp command there in a command window. This is the most complete test, but usually we dont have permission to authenticate using an account that is being used to run the SQL Server service.

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file - Import by BCP

After many google searches and my many attempts
I found the problem:

1. I write the code by Bulk insert and not with BCP command,
   this is the full Code:

---

declare @SQL nvarchar(max) = '

CREATE TABLE #TempTable
( [Field1] nvarchar(max) NULL,
    [Field2] nvarchar(max) NULL,
    [Field3] nvarchar(max) NULL ); 

BULK INSERT #TempTable  FROM ''<FullPath>\FileName.csv'' WITH --if the path is in the network - need to write the Full-path of the drive
(
KEEPIDENTITY,
FIELDTERMINATOR = '','',
MAXERRORS = 10000,
KEEPNULLS, 
ROWTERMINATOR=''\n'',
FIRSTROW = 2,
CODEPAGE = ''1255''
);

select * from #TempTable
Insert into TableNameInDB(Field1,Field2,Field3)
select * from #TempTable
'

EXEC sp_executesql @SQL

1. The path of the file was with the full path of the drive (if the drive map in the network)
2. I save the file by csv file

Importing data with bcp

Lose the single quotes around the filename. Either use no quotes, or use double quotes ("") if the name contains spaces and such.

---

Related Topics