August 8, 2008 at 11:41 am
I am trying to use the simple BULK INSERT command to load the data from a flat file to sql server tables. but i keep getting this error:
Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
i have also googled for this and many people have faced this problem earlier but the solution is not mentioned clearly in those posts.
August 8, 2008 at 11:50 am
The first line of the error seems to indicate a difference between the data in the file and how it is defined in either the target table or the format file used. If we can take the error at face value, a record in your text file is too long for the target column.
--SJT--
August 8, 2008 at 11:54 am
the datatypes for the columns are bigint and smallint. If if specify the size for these datatypes while creating the table then i am not able to do so. it gives me an error. so i didnt specify the size. it looks like a simple task but giving errors. i also tried using the bulk insert task from SSIS, but having problems with that also
August 8, 2008 at 12:14 pm
sonia (8/8/2008)
the datatypes for the columns are bigint and smallint.
What type is defined for the data in column 3? Also, are you using a format file or just letting BULK INSERT run against the target table?
Edit: Could you provide the T-SQL you're using, some sample data, and the format file layout (if applicable)?
--SJT--
August 8, 2008 at 12:27 pm
i am not using format file, just using the bulk insert.
this is what i am doing:
CREATE TABLE dbo.table
(
col1 bigint NOT NULL,
col2 smallint NOT NULL,
col3 smallint NOT NULL
)
GO
BULK INSERT dbo.table
FROM 'd:\data\tables.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
the sample data is
1233000087 23001 3
1233000088 23002 10
1233000089 23003 7
1233000090 23004 345
August 8, 2008 at 12:39 pm
i am sorry... there was a typo... the actual row terminator i used is:
ROWTERMINATOR = ''.
August 8, 2008 at 12:40 pm
the rpevious post showed it wrong again. the row terminator in my data is[ new line]
August 8, 2008 at 12:42 pm
I see that the forums program takes issue with the newline character. Are you using newline for the ROWTERMINATOR?
ROWTERMINATOR = '' --Can't get the newline to show up here. Someone know why?
--SJT--
August 8, 2008 at 12:48 pm
sonia (8/8/2008)
the rpevious post showed it wrong again. the row terminator in my data is[ new line]
Given the correction you noted, the code works as expected for me. The error from the original post doesn't point to the bulk insert option being disabled. Any other ideas out there?
--SJT--
August 8, 2008 at 12:48 pm
i also had the same problem while posting. somehow cant make it to show up.
yes i am using new line for ROWTERMINATOR
August 8, 2008 at 1:03 pm
sonia,
Well, then this is almost certainly a problem with the data in the source file. You might want to try using int instead of smallint to test the contents of the file. If it works with int, you know there's some value that's too big for smallint in your source.
It's the simple things that often trip me up.
--SJT--
August 8, 2008 at 1:33 pm
i checked my data file again and it is the problem with my row terminator. the row terminator is line feed but not new line. thanks a lot for ur input. i shall be more careful from next time before posting a problem
August 8, 2008 at 1:41 pm
sonia,
Aha! And here I was trying to duplicate the text of your error by manipulating everything but the row terminator in the source file. A little knowledge gained for me.
Good Hunting,
--SJT--
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply