BUG: Only 65,534 rows are inserted when you try to insert more than 65,535 records in a SQL Server table by using the SQLBulkOperations ODBC function (875411)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft Data Access Components 2.8
  • Microsoft Open Database Connectivity 3.0

SYMPTOMS

When you use the SQLBulkOperations Open Database Connectivity (ODBC) function to insert more than 65,535 records in a Microsoft SQL Server table, the SQLBulkOperations function inserts only 65,534 records in the SQL Server table. You may receive the following error message:
[HY000] [0]
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
An error message that is similar to the following may also be logged in the SQL Server error log file:
2004-07-19 21:13:35.86 spid52 Error: 17805, Severity: 20, State: 3
2004-07-19 21:13:35.86 spid52 Invalid buffer received from client.
Note This problem occurs on computers that are running the 32-bit version of Microsoft Windows.

CAUSE

On computers that are running the 32-bit version of Windows, the SQLSetPos function is invoked by the SQLBulkOperations function. The SQLSetPos function internally converts the SQL_ATTR_ROW_ARRAY_SIZE attribute from the UDWORD data type to the SQLUSMALLINT data type.

The value that can be stored in the SQLUSMALLINT data type ranges from 0 to 65,535. Therefore, if the value of the SQL_ATTR_ROW_ARRAY_SIZE attribute is more than 65,535, the data type conversion truncates the data in the SQL_ATTR_ROW_ARRAY_SIZE attribute.

Note The SQL_ATTR_ROW_ARRAY_SIZE attribute indicates the number of rows that you want to insert in the SQL Server table.

WORKAROUND

To work around this problem, do not insert more than 65,535 records in the SQL Server table by using the SQLBulkOperations function. To insert more than 65,535 records in a SQL Server table, you must run the SQLBulkOperations functions several times.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

On a computer that is running the 64-bit version of Windows, the SQLSETPOSIROW data type is equal to the SQLULEN data type, and the data type of the SQL_ATTR_ROW_ARRAY_SIZE attribute is equal to SQLULEN. Therefore, in the SQLSetPos function, the type conversion of the SQL_ATTR_ROW_ARRAY_SIZE attribute to the SQLSETPOSIROW data type does not cause any data loss. Therefore, the SQLBulkOperations function runs successfully on a computer that is running the 64-bit version of Windows.

Note The SQLBulkOperations function has no relationship to the SQL Server bulk copy functions. Applications must use the SQL Server-specific bulk copy functions to perform bulk copy operations.
Modification Type:MajorLast Reviewed:7/22/2004
Keywords:kbfunctions kbtable kberrmsg kbtshoot kbbug KB875411 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.