The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob

  Wednesday, September 19, 2018

 


Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

 

Eugene March 2003_16.JPG

  bob Links
http--dor.state.mo.u...
There are more old drunks than old doctors. - Anonymous
Wider View Insert

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
SQL Server
     Temporary Database
     Views
     Backup
     SQL Mail
     DTS - Data Transformation Services
     Memory
     Security
     Excel
          Excel Buffer Errors
     Jobs SQL Agent
     MSDE
     Install
     DBCC
     User Management
     System Databases
     BCV
     Deadlocks
     Temp Tables
Windows OS

Insert Category

Will be added as a sub-category of, Excel
Excel Buffer Errors
Why do I receive buffer size errors when trying to import an Excel spreadsheet? Chances are, this is caused by a registry key that is set incorrectly for your Excel Jet driver. Generally, the full error will look like this: "Error at source for row number 9.Errors encountered so far in this task :1" General Error: -2147217887(80040E21) Data for Source Column 1('Col1') is too large for the specified buffer size. When importing an Excel spreadsheet using the Jet driver, the OLE DB provider will scan the first few rows to look for any large columns. If it doesn't find any, it sizes the buffer to be smaller. But if a large text row is later in the spreadsheet you'll experience the problem. Fix You can either move one of your larger rows up to the top of the spreadsheet or edit a registry key to fix it forever. The registry setting to modify is: HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows By default, the value is 8, which means it will scan 8 rows to size the buffer for columns larger than 255 characters. By clicking the TypeGuessRows data value and selecting Decimal, you can specify a new number. Before you click OK, change it back to a Hexidecimal. Please note that editing the registry could be dangerous. Always backup your registry before making any changes to it. It is suggested that you make an emergency repair disk. For more information on this problem, see Microsoft support article Q281517.