Bulk insert data conversion error (truncation) for row 1

[girl]

Hi i have a problem with MS SQL server 2000 and hope to seek for some advise.

i have the following samples
aa|0|abcdefg|
b|0|abcdefg|

i used the bulk insert in the query analyser..


BULK INSERT adl_ntid from 'C:/abc.unl with
(

FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

but they prompt me

Bulk insert data conversion error (truncation) for row 1, column 3 (NAME).

when i open in notepad, there is a square icon seperating my data. and i think this is the reason for the unsucessful insertion into my database.

i had looked into UNICODE DATA. but could not solve the problem

hope i can get some help

[Motoma]

My first piece of advice would be to analyze the text file using a hex editor to try to determine what character is getting put there.

My first thought is that the file was saved on a Unix system and that you may have incompatibilities with the different style line breaks.

[girl]

hI
thnx for your help. i had tried my textfile using the hex editor and it shows that my rows terminator is a linefeed.
i can insert the data using DTS import export file but when i tried implmenting in query analyser it still prompt me the same error

BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' "+CHAR(10)+" '
)


BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' {LF}'
)

once again thnx for your help

[Motoma]

I overlooked the error message...What is the data type for the field that is giving the problem?

[girl]

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

the data types for the field names i am using are vchar.
thnx!

[Motoma]

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

the data types for the field names i am using are vchar.
thnx!

What size? This is a truncation error. That typically means that the data you are trying to insert will not fit in the field you are putting it in: you may have a varchar(4) and be inserting 8 characters.

[girl]

when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.

[Motoma]

when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.

What settings have you enabled for you DTS package? Perhaps you could run the SQL Profiler and see what the DTS package is calling for the INSERT?