PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error (281517)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
  • Microsoft OLE DB Provider for Jet 3.51
  • Microsoft OLE DB Provider for Jet 4.0
This article was previously published under Q281517

SYMPTOMS

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



When you import data from a Jet OLEDB 4.0 data source by using Data Transformation Services (DTS) and the Microsoft OLE DB provider for Jet, the data pump task may fail with this 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.
If the source has fields with more than 255 characters, the destination data for these fields may be truncated to 255 characters. DTS fails on the first row that has a field with more than 255 characters. To verify this, look up the row number that displays in the error message.

CAUSE

The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is:

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.

WORKAROUND

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To change the value of TypeGuessRows, use these steps:
  1. On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
  2. Open the following key in the Registry editor:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
  3. Double-click TypeGuessRows.
  4. In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
  5. Click OK, and then exit the Registry Editor.
A second way to workaround this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.

MORE INFORMATION

The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.

Steps to Reproduce Problem

  1. Open a new Microsoft Excel workbook and create a sheet with three columns and 11 rows and include the first row with column names.
  2. Insert data into the cells so that the first 8 rows have data less than 255 characters in length.
  3. Insert data that is greater than 255 characters in length into the 10th and 11th rows. Save this sheet as ExcelSource.xls.
  4. Open the SQL Server Enterprise Manager and expand the Server name. Right-click Data Transformation Services, and then click Import Data.
  5. For the Source, select the Excel 97-2000 provider and provide the name for the Excel sheet that you saved earlier (ExcelSource.xls).
  6. For the destination select Microsoft OLEDB provider for SQL, and then select the destination database.
  7. Click Copy Tables and Views from the source DB, and then click Next.
  8. In the Select Source Tables dialog box, select Sheet1$, click Next, and then run the package immediately. The package execution fails with the error message shown in the "Symptoms" section.

  9. Drop the table Sheet1$ from the destination database by using the command Drop Table Sheet1$.
  10. Follow the resolution in the "Workaround" section and make the change to the registry key for TypeGuessRows.
  11. Now, repeat steps 4 through 8. This time the package execution is successful.

REFERENCES

For additional information about this behavior specific to Microsoft Excel, click the article number below to view the article in the Microsoft Knowledge Base:

189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver

Modification Type:MajorLast Reviewed:12/3/2003
Keywords:kbJET kbprb KB281517
©2003 Microsoft Corporation. All rights reserved.