Select the product you need help with

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

Article ID: 281517 - View products that this article applies to.

This article was previously published under Q281517

SYMPTOMS

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

WORKAROUND

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756

(http://support.microsoft.com/kb/322756/ )

How to back up and restore the registry in Windows

To change the value of TypeGuessRows, use these steps:

On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
Open the following key in the Registry editor:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Note For 64-bit systems, the corresponding key is as follows:

HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel
Double-click TypeGuessRows.
In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
Click OK, and then exit the Registry Editor.

A second way to work around 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.

Back to the top | Give Feedback

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

Open a new Microsoft Excel workbook and create a sheet with three columns and 11 rows and include the first row with column names.
Insert data into the cells so that the first 8 rows have data less than 255 characters in length.
Insert data that is greater than 255 characters in length into the 10th and 11th rows. Save this sheet as ExcelSource.xls.
Open the SQL Server Enterprise Manager and expand the Server name. Right-click Data Transformation Services, and then click Import Data.
For the Source, select the Excel 97-2000 provider and provide the name for the Excel sheet that you saved earlier (ExcelSource.xls).
For the destination select Microsoft OLEDB provider for SQL, and then select the destination database.
Click Copy Tables and Views from the source DB, and then click Next.
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.
Drop the table Sheet1$ from the destination database by using the command Drop Table Sheet1$.
Follow the resolution in the "Workaround" section and make the change to the registry key for TypeGuessRows.
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

(http://support.microsoft.com/kb/189897/EN-US/ )

XL97: Data Truncated to 255 Characters with Excel ODBC Driver

Back to the top | Give Feedback