|
|
 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
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.
|
|
|
|
|