Sql-server – “Unable to Open BCP host data-file” using xp_cmdshell from SSMS but command line works

bcppermissionssql serversql server 2014xp-cmdshell

I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written.

I noticed the files were piling up and BCP wasn't picking them up so I tested in Management Studio using the following:

DECLARE @sql VARCHAR(MAX)
DECLARE @path VARCHAR(512) = 'C:\BCPFiles\'

--Use BCP to copy files in character format from the target directly into the table. 
SET @sql = 'bcp [MyDB].[dbo].[MyTable] in ' + @path + 'bcpFile.dat -c -T'

EXEC master..xp_cmdshell @sql

And got this error message

BCP Error

Executing from the command line on the same server using the same command works no problem.

Here's a few other observations:

  1. I can run EXEC master..xp_cmdshell 'dir C:\*.*' and the results return as expected.
  2. If I copy and rename the file to something different, BCP picks it up
  3. The file isn't being left open by my application: I can move it, rename it, edit and save it, delete it etc… without any complaints from Windows.
  4. The internal format of the file is obviously okay (see 2.)
  5. NT SERVICE\MSSQLSERVER has Full Control permissions to the directory.
  6. The Maple Leafs traded Dion Phaneuf yesterday

Any help is appreciated.

Best Answer

I just got your code to work on my end.

Try this:

Option A

  1. Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to the user you will use in SQL Server Management Studio to run the query
  2. Re-run your query in SQL Server Management Studio

Option B

Or, just to get past the following error try:

Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
  1. Delete/move your bcpfile.dat file

  2. on the command line type (to create a dummy file--yes there will be import errors, but just to get past opening the file...)

    echo .> c:\bcpfiles\bcpfile.dat

  3. Re-run your query in SQL Server Management Studio

If option A works, then it's a permissions issue. If option B works (getting past the original error) then you have an open file--even though it doesn't appear to be open. There are utilities that help out with finding these open files, but a cheap and easy way to test is just to reboot the server--hopefully it's a dev/test box.

Related Question