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

StackExchange Database Administrators Q#128855, answer score: 12