Bulk Insert Quandry

Question

Bulk Insert Quandry

Philip Kelley

SSCertifiable

Points: 6351
More actions
December 2, 2005 at 4:48 pm

#188624

I've got a bulk insert command that works when I use the bcp utility (either from the command window or via xp_cmdshell) but does not work when I use the BULK INSERT command. With the latter, I get the following error:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
I've trimmed both statments down to minimal settings:
BCP: specifies database.owner.table, "in" and file name, -f and format file, _S and -T for local server.
BULK INSERT: from clause specifies full (local) file path and name, formatfile specified the format file; run on the self-same server.
The format file is a complex beast, but the data is correctly loaded via bcp. Any ideas what's going on here? (I'll burn time in MS technet and knowledgebase, but have few hopes of finding anything useful in under an hour's search.)
Philip

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Not of much help here, I'm sorry to say.. But for the most parts when bcp complains about unexpected EOF, it has to do with the rowterminator not matching what it finds in the file.. so... I'm guessing that the bulk insert command may have similar issues.

Have stumbled upon similar things when trying to use bulk insert vs bcp, where bcp has always worked. Perhaps due to lack of experience with bulk insert, so I haven't gotten around to learn all it's 'quirks' like I have with good ol' bcp - the end result has always been that I have used what worked - bcp.

/Kenneth

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 2

Turned out to be a combo of weird and stupid.

I'd worked out the code and format file (247 columns into 21 with unlikely column and row terminators). All values were within double-quotes and separated by commas, and the final column was always "EOI", so we had file contents like:

"asdf","qwert","","","","<repeat a lot> ","","","EOI"

I set the col delimiter to be "," and the row delimiter (defined as the final col delimiter) to be ","EOI", and that worked rather nicely in bcp. (The first col gets messed up, but that's OK because we fortuitously don't need it.) But this failed in BULK INSERT... until I added the forgotten/overlooked /r/n (Carriage Return/Line Feed), at which point everything worked in both bcp and BULK INSERT.

Meaning:

1) bcp will somehow factor in the CR/LF for you where BULK INSERT won't, and

2) I probably should have included all that fussy detail in my first post

Philip

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

From BOL (your best buddy)

BCP

-r row_term

Specifies the row terminator. The default is \n (newline character). Use this parameter to override the default row terminator.

BULK INSERT

ROWTERMINATOR [ = 'row_terminator' ]

Specifies the row terminator to be used for char and widechar data files. The default is \n (newline character).

It seems like there's no difference in the default settings for either.

Though, none of the defaults is what you finally used (\r\n)

/Kenneth