11

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.

1
  • Can you try putting the full bcp path in the script ? e.g. `C:\Program Files\Microsoft SQL Server\100\Tools\Binn`
    – Kin Shah
    Feb 10 '16 at 14:55
11

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.

2
  • I found out the problem this morning. The other server that this is running on successfully automatically cascades permissions to files in the folder. This server was not doing that. Files being moved into the folder did not have permissions for NT SERVICE\MSSQLSERVER, but files created there (read: copied and renamed) did. Permissions issue. Option A would have solved the problem.
    – Brandon
    Feb 11 '16 at 15:01
  • In this case, the first option that worked for me I just change my location to the drive D:/ instead of drive C:/ Jul 30 at 6:47
0

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.
1
  • 3
    This doesn't add anything new, though its not wrong as such. Jul 3 '17 at 9:42
0

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.

1
  • 1
    I think you'll find the question does in fact use a file name: @path + 'bcpFile.dat -c -T'
    – Hannah Vernon
    Aug 22 '17 at 19:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.