Buffer Size Problem

Question

Post reply

Buffer Size Problem

PaulSp

SSCrazy

Points: 2499
More actions
May 24, 2006 at 4:03 am

#366267

Hi

I've been searching this site and the Web for info on an error message I get when importing from Access 2003 into SQL Server 2000.

'Data for Source Column 3('Col3') is too large for the specified buffer size'

A memo field in Access is larger than 255.

I have followed advice about putting the field to the first column. This doesn't work - the error just returns the new column number. In fact, I've tried just importing the first column - no good.

I am wary about making Registry changes as comments on the Web say this doesn't work either.

Does anybody have the solution for this.

Paul

Viewing 11 posts - 1 through 10 (of 10 total)

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

PaulSp SSCrazy Points: 2499 More actions · Answer 1

Paul again:

I've found this for a 'Question of the Day' dated 8/4/2004.

'Category: DTS

You are a DTS programmer that is trying to transform data from a large Excel file to SQL Server. You try to execute the package for the first timebut receive the following error message:

"Error at source for row number 9.Errors encountered so far in this task :1"General Error: -2147217887(80040E21)Data for Source Column 3('Col3') is too large for the specified buffer size.

What is the method to fix this? '

Maybe there is an answer also. Does anybody know where I can find it?

Thanks Paul

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 2

DTS samples the first 8 (I think) rows and sets its' buffer size based on that sampling. I don't remember exactly where it is (I don't use DTS very often), but you need to go into "Disconnected Edit" and find the properties of the import. You can adjust the size of the column there.

Another way to do it is move the row that has the largest column size to the first row in the import data (if you have that ability).

Hope this helps.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

PaulSp SSCrazy Points: 2499 More actions · Answer 3

Hi

Thanks for your reply. I tried to import just one column to test the first 8 column hypothesis. No go, unfortunately.

I also tried the 'Disconnected Editing' - right-click in open area of DTS package. I changed all the field sizes in Transformations - to no avail. Somehow I need to change the Buffer Size.

Thanks again

Paul

Filet SSC Enthusiast Points: 184 More actions · Answer 4

There is a possible solution in the Forum article entitled

DTS error when attempting to import from MS Access into SQL Server

posted 10/5/2005

by "Chris":

as a workaround, I took the following steps:

In my DTS package, I alter the applicable table to add a new column with a data type of 'text',
import into this 'text' column,
copy all data from this 'text' column into the corresponding nvarchar(4000) column,
drop the 'text' column.

This process works fine, although I'm disappointed that my DTS package has to go through the extra trouble.

Cheers

Filet

PaulSp SSCrazy Points: 2499 More actions · Answer 5

Hi Filet

Many thanks for your reply. I agree and sympathise with your disappointment. The whole thing seems pretty poor.

The work around I have used is to split the Access table and memo fields into several subsidiary fields using an Acess Query (left(fieldname,255). Mid(fieldname,255,255) etc. etc.). Doing it this way I can run a timed DTS package to pull the required data out of the Access database and into SQL Server. The package can then reassemble the fields into the SQL table. Pretty disgusting - but it works.

If I import by switching the data into a large text field, I will have to manually do this in the Access database each time.

Thanks again, and I hope Microsoft get this sorted.

Paul

Al Cadalzo SSC Enthusiast Points: 109 More actions · Answer 6

I had the same problem. Then I saw in your post that you had tried 'Disconnected Edit' which gave me the idea to look at the properties in there. I looked in Disconnected Edit and saw that under 'Connections' I found the connection for my Text Source file and there was 'OLE DB Properties' and under there I found 'Max characters per delimited column'. It was set to 255 so I increased it to 4000 and then it worked fine. Thank you for posting that!

PaulSp SSCrazy Points: 2499 More actions · Answer 7

Hi

I agree, this is a great site. However, I still don't seem to be able to find 'Max characters per delimited column' under OLE DB properties for the Source. I'm importing from Access 2003. There is a Max Buffer Size, but that doesn't seem to work. I'm still using my workaround, but if there is a better solution I'd like to find it. Are you importing from Access? Is this option under another heading?

Paul