Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Imported data from .xls exceeds buffer size?

    Hi,

    Trying to import data from Excel spreadsheet into SQLServer2000. Spreadsheet is Excel 2002, 19 columns by about 500 rows.

    I get an error that the data in column 19 "is too large for the specified buffer size."

    Specified buffer what?

    I thought it just meant that the column in my database table was too small - so I knocked it up to varchar(8000), and then to text. Still no good.

    The column in question - well, it's big, maybe a few thousand chars in some rows, but I'd be surprised if it was more than 8000 - and then the text datatype should hold it fine, right?

    What's the "buffer size"? Can I alter it?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What row number does it first fail ? The provider that dts is using is guessing as to the size of the column that it is trying to read - if this is not one of the 1st eight rows it hits, you will have problems. If this is your scenario, you will need to modify the typeguessrow key in your registry.

    Let me know.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    It's line 388 - which is the first of the longer entries in that column.

    Thanks for your explanation - so it uses the first 8 rows to <guess?> how much memory to allocate to that column, and if the first entires are quite small it'll fall over when it gets to bigger ones further down?

    Can I not simply move a row with lots of data in the offending column, to the top? Then it'll know to expect similar entries further down? Or am I missing something?

    If so, could you possibly point me in the direction of any further info on modifying the typeguessrow key? I don't muck about with registries all that often and the thought of doing it on our live database server doesn't exactly inspire confidence...

    Thanks

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Yes you can move it to the top, but that can be a pain if you process alot of these files. For the registry - run regedit and search for typeguessrows - there might be a couple, so make sure you are on the correct one - excel version x. Then just modify the typeguessrows to a number that fits your situation.

    Let me know if you have any problems.

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Ah, I just moved it to the top in the end. The order these things are in doesn't matter much, and I'm only going to be doing this once (hey, I leave at the end of the week ). But thanks for the registry info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •