I'm trying to use a non-XML bcp format file to import data into LocalDB on Win7 64 bit. Simplest possible use case.
OS Name: Microsoft Windows 7 Home Premium
OS Version: 6.1.7601 Service Pack 1 Build 7601
LocalDB version: Microsoft SQL Server 2014 (12.0.2000.8)
BCP version: 12.0.2000.8
Basically, latest version of everything downloaded from Microsoft SQL Server 2014 site a few days ago.
I'm able to connect to the LocalDB instance via bcp to create a format file, but the generated format file doesn't work when trying to re-import the simplest possible data using it. No matter what I try, bcp loads zero rows, fails silently and prints no error information to the specified error file.
/* create the table */
use try_db;
create table try(num integer);
/* create the format file based on the table. */
bcp try_db.dbo.TRY format nul -n -T -f TRY.fmt -S (localdb)\default_db
/* above command creates a file TRY.fmt with the following contents */
12.0
1
1 SQLINT 1 4 "" 1 num ""
/* then I create a file data.txt, with just the number 99 in it, followed by a Windows line terminator (\r\n) */
/* then try importing the file into the table */
bcp try_db.dbo.TRY in data.txt -f TRY.fmt -T -S (localdb)\default_db -e errors.txt
Result:
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
Nothing is written to errors.txt. I am just not able to get bcp to import anything at all using a format file!
I haven't tried it with SQL Server itself (only with LocalDB) but it shouldn't matter for stuff as simple as this.
I tried editing the TRY.fmt file line as follows:
1 SQLINT 1 4 "\r\n" 1 num ""
But that didn't help either.
I am able to get it to successfully import using -c instead of -f:
bcp try_db.dbo.TRY in data.txt -c -T -S (localdb)\default_db -e errors.txt
Any thoughts on (a) why bcp won't import using the format file, and (b) why it prints no errors to the specified error file? There must be something really simple I'm getting wrong here.
Please, no recommendations to use BULK INSERT or SSIS (etc) instead. bcp should just work as documented!
The format file is describing the source data not the destination. When you use -c or datafiletype='char' your input datatypes must be SQLCHAR. Native datatypes are only valid when using -n or datafiletype='native'. A source file in native format is always binary so bcp needs to know the data type of each field in order to read the correct amount of bytes and interpret them correctly.