By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,414 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Bulk insert data conversion error (truncation) for row 1

P: 8
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
Jun 5 '07 #1
Share this Question
Share on Google+
7 Replies


Motoma
Expert 2.5K+
P: 3,235
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.
Jun 5 '07 #2

P: 8
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
Jun 6 '07 #3

Motoma
Expert 2.5K+
P: 3,235
I overlooked the error message...What is the data type for the field that is giving the problem?
Jun 6 '07 #4

P: 8
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!
Jun 6 '07 #5

Motoma
Expert 2.5K+
P: 3,235
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.
Jun 6 '07 #6

P: 8
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.
Jun 6 '07 #7

Motoma
Expert 2.5K+
P: 3,235
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?
Jun 6 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.