"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works

Question

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:

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.

Answer 1

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.

Answer 2

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

• related to this error, it because of the BCP cannot open the output directory. like: exec master..xp_cmdshell 'bcp "select * from [TestDB].[dbo].[tbl]" queryout "C:\Test\"'
• change the output directory from D:..\ to C:..\, it will be OK as I used to meet this.

Answer 3

This happens because the file name is missing.

This is bad:

exec master..xp_cmdshell 'bcp "select * from [TestDB].[dbo].[tbl]" queryout "C:\Test\"'

You can not write to a folder. You must write to a file.

This is OK:

exec master..xp_cmdshell 'bcp "select * from [TestDB].[dbo].[tbl]" queryout "C:\Test\others.txt"'

because you've specified a file name.