May 24, 2006 at 4:03 am
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
May 24, 2006 at 4:35 am
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
May 25, 2006 at 6:26 am
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.
May 25, 2006 at 9:37 am
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
May 26, 2006 at 8:14 pm
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:
This process works fine, although I'm disappointed that my DTS package has to go through the extra trouble.
May 30, 2006 at 3:10 am
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
June 10, 2006 at 9:43 am
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!
June 12, 2006 at 2:55 am
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
March 19, 2007 at 10:08 pm
hi,
One solution to buffer size problem as listed in microsoft is available.
http://support.microsoft.com/kb/281517 .
You can try changing the Registry properties to solve the problem
Cheers,
Arthi.
March 20, 2007 at 4:47 am
Thanks, Arthi
I had to do a lot of unnesessary work to achieve this originally. When I have to do it again, I'll give your indicated method a try.
Very many thanks for taking the time to contribute here.
Best wishes
Paul
May 25, 2007 at 8:41 am
Thanks Al Cadalzo, that worked great
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply