Bulk insert error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk insert error

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-03-29 : 17:17:00

Earlier rowterminator for the text file was '\r\n', I was able to bulk insert
with no rowterminator
Now it is changed to '\n', When I use the following syntax

create table #tmp1 ([rows] varchar(5000) )
BULK INSERT #tmp1 from 'c:\test_file.txt'
with
(rowterminator = '\n')

I am getting following error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
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.

any help is appreciated.

thanks

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-29 : 22:19:11

The linefeed character is the default rowterminator, so you shouldn't need to specify it at all in this case.

Are you certain that the lines are all less than 5000 characters between linefeeds now? Whenever I change file formats (such as EOL going from \r\n to just \n), I'm always skeptical and usually take time to QA my intermediate file before I submit it.

-PatP

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-03-30 : 18:57:01

Can you please let me know the way to find the row delimiter.
I did find from the DTS wizard where only it when thru with {LF}

thanks for Ur time.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-30 : 22:16:59

SQL Books Online, the product documentation.

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources