bulk insert error with double Newline character

Question

bulk insert error with double Newline character

Orange Crusher

SSC Veteran

Points: 250
More actions
June 12, 2008 at 10:51 am

#187093

When running
BULK INSERT AuditDBA.dbo.tblPS_FILE_LOG FROM 'C:\PSfiles.log'
WITH (FIELDTERMINATOR = '\r')
I get error
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 100, column 1 (LOG_LINE).
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
because the last lines of the text file ends like
----------------------- Newline character
0 File(s) copied Newline character
(null) Newline character
Newline character
When I remove the last Newline character, I script runs without error.
What should I do to allow the last Newline character?
See attachment for screen shot of text file
thanks for any help
Orange Crusher
i love it!

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

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

Orange Crusher SSC Veteran Points: 250 More actions · Answer 1

No help needed. I found a work around.

The double new line was created by combining 3 text files. So instead of combining the 3 text files then doing 1 bulk insert. I am doing 3 bulk inserts.

Orange Crusher
i love it!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Thanks for the feedback... glad you found a work around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)