Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you rebuild an index for a table in a BizTalk Server database: "Cannot insert duplicate key row in object"

Error message when you rebuild an index for a table in a BizTalk Server database: "Cannot insert duplicate key row in object"

View products that this article applies to.

Symptoms

You rebuild the index for a table in a Microsoft BizTalk Server database that was implemented by using Microsoft SQL Server 2005 Service Pack 1 (SP1). After you do this, an error message that resembles the following may be logged in the Application log on the computer that is running BizTalk Server:
Cannot insert duplicate key row in object 'dbo.table name' with unique index 'index name'
In this error message, table name may be one of the following:
  • TrackingMessageReferences
  • MessageParts
  • MessageProps
  • UniqueSubscription
  • ConvoySetInstances
  • Modules
In this error message, index name may be one of the following:
  • CIX_TrackingMessageReferences
  • CIX_MessageParts
  • CIX_MessageProps
  • IX_UniqueSubscription
  • CIX_ConvoySetInstances
  • IX_Modules

↑ Back to the top

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

↑ Back to the top

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.

↑ Back to the top

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_helpindex TrackingMessageReferences; -- CIX_TrackingMessageReferences on TrackingMessageReferences
GO
EXEC sp_helpindex MessageParts; -- CIX_MessageParts on MessageParts
GO
EXEC sp_helpindex MessageProps; -- CIX_MessageProps on MessageProps
GO
EXEC sp_helpindex UniqueSubscription; -- IX_UniqueSubscription on UniqueSubscription
GO
EXEC sp_helpindex ConvoySetInstances; -- CIX_ConvoySetInstances on ConvoySetInstances
GO
EXEC sp_helpindex Modules; -- IX_Modules on Modules
GO
Verify that the Index_description column for the indexes contains ignore duplicate keys.

To avoid this problem, make sure that you update SQL Server 2005 to the latest service pack before you run the re-index operations again. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
913089 How to obtain the latest service pack for SQL Server 2005

↑ Back to the top

References

For more information about the issues that may occur when you connect to the BizTalkMsgBox Db database, click the following article number to view the article in the Microsoft Knowledge Base:
917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

↑ Back to the top

Keywords: KB936124, kbprb, kbtshoot, kbbts

↑ Back to the top

Article Info
Article ID : 936124
Revision : 3
Created on : 4/28/2009
Published on : 4/28/2009
Exists online : False
Views : 593