Microsoft KB Archive/936124

= Error message when you rebuild an index for a table in a BizTalk Server 2006 database: &quot;Cannot insert duplicate key row in object&quot; =

Article ID: 936124

Article Last Modified on 5/14/2007

-

APPLIES TO


 * Microsoft BizTalk Server 2006 Standard Edition
 * Microsoft BizTalk Server 2006 Enterprise Edition
 * Microsoft BizTalk Server 2006 Developer Edition

-



SYMPTOMS
You rebuild the index for a table in a Microsoft BizTalk Server 2006 database that was implemented by using Microsoft SQL Server 2005. After you do this, an error message that resembles the following may be logged in the Application log on the computer that is running SQL Server 2005:

Cannot insert duplicate key row in object 'dbo. ' with unique index ' '

In this error message,  may be one of the following:
 * TrackingMessageReferences
 * MessageParts
 * MessageProps
 * Subscription
 * ConvoySetInstances
 * Modules

In this error message,  may be one of the following:
 * CIX_TrackingMessageReferences
 * CIX_MessageParts
 * CIX_MessageProps
 * IX_UniqueSubscription
 * CIX_ConvoySetInstances
 * IX_Modules



CAUSE
This issue may occur if the following conditions are true:  Tracking is enabled for a send port or for a receive port. You experience the problem that is described in the following Microsoft Knowledge Base article:

936894 FIX: Indexes that at rebuilt after you run a maintenance plan that contains the Rebuild Index task have the same Ignore Duplicate Key setting as the Ignore Duplicate Key setting of the Rebuild Index task





RESOLUTION
To resolve this issue, enable the Ignore duplicate values option for the index. To enable the Ignore duplicate values option, follow these steps:
 * 1) Start Microsoft SQL Server Management Studio.
 * 2) Expand the SQL Server installation that you want, expand Databases, expand BizTalkMsgBoxDb, expand Tables, expand the table that is referenced in the error message, and then expand Indexes.
 * 3) Right-click the index that is referenced in the error message, and then click Properties.
 * 4) Click Options, click to select the Ignore duplicate values check box, and then click OK.



MORE INFORMATION
To verify that the Ignore duplicate values option is enabled on the index, use the sp_help Transact-SQL command. For example, use the following query to check whether the Ignore duplicate values option is disabled on the CIX_TrackingMessageReferences index in the dbo.TrackingMessageReferences table. use BizTalkMsgBoxDb EXEC sp_help TrackingMessageReferences Verify that the Index_description column for the CIX_TrackingMessageReferences index contains the following entries: clustered, ignore duplicate keys, unique located on PRIMARY

