DTS vs BULK INSERT

[namtab]

I have a Unix text file which has a fieldterminator of || and a rowterminator of LF. If I use DTS the import works perfectly however if I use BULK INSTERT i get :-

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (c2).

I know it's got something to do with the row terminater but how come LF works for DTS?

 

[nigelrivett]

More tyhan likely because you've set up the bulk insert incorrectly.
Second place (but not by far) is a bug in bulk insert.

How are you defining the rowterminator?
Try it with bcp - then you can bcp out and in to check.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mrdenny]

Sounds to me like you don't have the bunlk insert command setup correctly. You will have to tell it what the row and field terminators are in the command.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[rasETL]

I may be wrong, but I do not think Bulk Insert is correctly understanding your field terminator. I think Bulk Insert is only looking for a single bar (|), not a double bar (||). The message appears to me to indicate it cannot convert the column that it think is between the two bars.