Post reply

Buffer Size Problem

  • 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

  • May 24, 2006 at 4:35 am

    #639709

    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

    #640006

    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

    #640097

    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

    #640465

    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: 

    1. In my DTS package, I alter the applicable table to add a new column with a data type of 'text',
    2. import into this 'text' column,
    3. copy all data from this 'text' column into the corresponding nvarchar(4000) column,
    4. 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

  • May 30, 2006 at 3:10 am

    #640616

    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

    #642973

    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

    #643023

    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

    #695839

    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

    #695873

    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

    #708985

    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