Knowledge Base

FIX: Table Corruption When Using BCP with SELECT INTO/BULK COPY, Nonclustered Index, and IGNORE_DUP_KEY

Article ID: 277547

Article Last Modified on 3/14/2006

APPLIES TO

Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q277547

BUG #: 57899 (SQLBUG_70)

SYMPTOMS

Table corruption errors may occur on index pages under the following conditions:

The SELECT INTO/BULK COPY option is on.
A bulk copy program (BCP) operation is done with the IGNORE_DUP_KEY option specified.
The target table has a nonclustered index.

The underlying data pages are in a consistent state; however, the following errors can be seen when you run DBCC Checkdb:

Server: Msg 8951, Level 16, State 1, Line 1
Table Corrupt: Table 'table_name' (ID 1941581955). Missing or invalid key in index 'index_name' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:365:0) identified by (RID = (1:365:0) ) has index values (store_number = 1211 and ssn = '440921916' and date_worked = Feb 7 2000 12:00AM?|???WeG and clock_in_time = '16:53' and clock_out_time = '19:08').

NOTE: The issue does not occur if there is a clustered index on the table in question.

RESOLUTION

To work around this problem, rebuild the affected index by using one of the following methods:

Drop and then re-create the index.
Use the DBCC DBREINDEX statement.
Use the REPAIR_FAST option with DBCC CHECKTABLE. (In some cases, it may be necessary to run this command more than once.)

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Keywords: kbbug kbfix KB277547

Microsoft KB Archive/277547

Contents