Help with XML format file, import int and fixed binary
I have a text file where each record of three fields looks like this: **0000###** [tab] **000####** [tab] **fixed-length binary 2037 bytes** [crlf] (The numbers in the first two fields are 6-characters, padded with leading zeros) I'm trying to import them into a table in 2008 R2 that looks like this: **int int binary(2037)** I've had no success with putting an XML format file together to do this. Here's may latest attempts at SQL CODE and FORMAT FILE: INSERT INTO BinTest (ID, Buffer, Bin2037) SELECT ID, Buffer, Bin2037 FROM OPENROWSET(BULK 'D:\Temp\Bin.txt', FORMATFILE='D:\Temp\Bin.Xml' ) AS t1; GO These are the errors I get with this attempt: *Msg 4864, Level 16, State 1, Line 9 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Buffer). Msg 4832, Level 16, State 1, Line 9 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 9 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 9 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".* Please help!
Never mind, I finally got it... My post had "20" for field 3, but that was just a typo for that run. The real problem was in field 6. I changed from CharFixed... to CharTerm... and it works. I still don't understand WHY CharFixed wouldn't work here, but that's alright for now.