BCP not working when {CR} is missing & {LF} is present at the end of a fixed length file

Question

SET @query='bcp Staging.DBO.MSP_Tin_Reference_Response_FileImport in '+@TinResponseFilePath+@TinResponseFileName+' -T -c ' 
EXEC MASTER..xp_cmdshell @query --,1

And it throws following error:

NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1     
NULL

I appreciate any help you could give me.

Answer 1

The -c option by default requires that each line is terminated in {CR}{LF}. You can however tell BCP to use a different row terminator using the -r option.

I suggest you try the import with options -c -r \n -t \t. This specifies that the import will use the character data type for all fields, but specifies {LF} as the row terminator and TAB as the field separator.

Another thing is that it is best to always put your file name between double quotes ("<file name>") otherwise the BCP command will fail if you have spaces in your file name.

So your command would look like this:

SET @query='bcp Staging.DBO.MSP_Tin_Reference_Response_FileImport in "'+@TinResponseFilePath+@TinResponseFileName+'" -T -c -r \n -t \t' 
EXEC MASTER..xp_cmdshell @query --,1

---

Update: it looks like you're still experiencing problems; now BCP is asking for file storage type of field XXX.

You can solve this by:

1. First creating a format file for your table. You only need to do this once.
2. Specify the format file in your import command.

---

Step 1 (taking C:\Temp\fi.fmt as an example):

SET @query='BCP Staging.DBO.MSP_Tin_Reference_Response_FileImport format nul -f "C:\Temp\fi.fmt"';
EXEC MASTER..xp_cmdshell @query;

---

Step 2. Run the import with the -f switch:

SET @query='bcp Staging.DBO.MSP_Tin_Reference_Response_FileImport in "'+@TinResponseFilePath+@TinResponseFileName+'" -f "C:\Temp\fi.fmt" -T -r \n -t \t' 
EXEC MASTER..xp_cmdshell @query