"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works 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



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

Here's a few other observations:


I can run EXEC master..xp_cmdshell 'dir C:\*.*' and the results return as expected.
If I copy and rename the file to something different, BCP picks it up
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.
The internal format of the file is obviously okay (see 2.)
NT SERVICE\MSSQLSERVER has Full Control permissions to the directory.
The Maple Leafs traded Dion Phaneuf yesterday


Any help is appreciated.
I just got your code to work on my end.

Try this:

Option A


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



Delete/move your bcpfile.dat file 
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
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.