Error-Unable to open BCP host file data, while executing BCP command

Question

I'm trying to copy data from a '.dat' file to a table in sql server I'm getting this error while executing the following commands:

declare @cmd varchar(100)
set @cmd = 'bcp dBFCLogging.dbo.TempBFCLogs in ' + '''D:\WorkArea\data.dat''' + ' -c -T'
exec xp_cmdshell @cmd

The exact error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

I ran a print command and checked the cmd variable, and that looks okay.

bcp dBFCLogging.dbo.TempBFCLogs in 'D:\WorkArea\data.dat' -c -T

I'm running this command on a DB server, so I have this file under the correct path on that DB server. I had read that there could be permission issues. So, I have enabled the user 'Everyone' to have Read/Execute access on that folder structure and also the file, but it still throws this error while execution. Please help!

Answer 1

Change "D:\WorkArea\data.dat" to "D:\\WorkArea\\data.dat"

Answer 2

If you put quotes around a file name they need to be double quotes, not single quotes. Your command should be:

declare @cmd varchar(100);
set @cmd = 'bcp dBFCLogging.dbo.TempBFCLogs in "D:\WorkArea\data.dat" -c -T';
exec xp_cmdshell @cmd;

There may be additional problems depending on the way data is formatted in the data file. In some cases you need to describe this in a format file and refer to this format file in your BCP command using the -f option.