You receive a "duplicate value for unique key member" error message when you log on to a Commerce Server site (838570)


The information in this article applies to:

  • Microsoft Commerce Server 2002 SP2
  • Microsoft Commerce Server 2000

SYMPTOMS

When you log on to a Microsoft Commerce Server site, you may receive the following error message:

Server Error in '/' Application.
--------------------------------------------------------------------------------
A duplicate value for unique key member Profile Property found while building indices for profile Profile Definition. Check the underlying store for uniqueness of the property.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: A duplicate value for unique key member Profile Property found while building indices for profile Profile Definition. Check the underlying store for uniqueness of the property.

Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: [COMException (0xc1004043): A duplicate value for unique key memberProfile Property found while building indices for profile Profile Definition. Check the underlying store for uniqueness of the property. ]
Microsoft.CommerceServer.Interop.Profiles.ProfileServiceClass.GetProfileByKey(String bstrKeyMemberName, Object sValue, String bstrType, Boolean bReturnError) +0
Microsoft.CommerceServer.Runtime.Profiles.Profile..ctor(ProfileContext profileService, String keyName, String keyValue, String profileType) +74

[CommerceProfileSystemException: Failed to retrieve profile.]
Microsoft.CommerceServer.Runtime.Profiles.Profile..ctor(ProfileContext profileService, String keyName, String keyValue, String profileType) +277
Microsoft.CommerceServer.Runtime.Profiles.ProfileContext.GetProfile(String keyName, String keyValue, String profileType) +183

CAUSE

Commerce Server will find a duplicate key when a user enters a value and the same value exists in the database with spaces padded at the end of the character string. This behavior occurs if the data type of the column designated as a join key is configured in SQL to be a CHAR data type.

If the Profile database that the Commerce Server site uses contains a value that has spaces appended to its character string, and you enter the same value without the appended spaces, Commerce Server determines that you have entered a duplicate key. This behavior may occur if the column of the data member that the profile property references, and that is designated as a join key, is configured in SQL Server to use a char data type.

RESOLUTION

To resolve this problem, change the value of the data type from char to nvarchar for the column of the data member that the profile property references. To do this, follow these steps:
  1. In Commerce Server Manager, expand Commerce Server Manager, expand Global Resources, expand the profile resource of the site, expand Profile Catalog, and then expand Profile Definition.
  2. Open the profile definition that appears in the error message, and then expand General Information Group.
  3. In the left pane, click the profile property that appears in the error message, and then expand Advanced Attributes.
  4. Click the button next to the Mapped Data value.
  5. In the Data Source Picker -- Web Page dialog box, notice the name of the table and the name of the column. Click Cancel.
  6. In the left tree view, locate Microsoft SQL Servers, expand SQL Server Groups, expand the name of the server, expand Databases, and then expand Profile Database.
  7. Locate the table name that you viewed in step 6. Right-click this table name, and then click Design Table.
  8. In the Design Table dialog box, locate the column name that you viewed in step 6, and then change the data type of the column from char to nvarchar.
  9. Close the Design Table dialog box.
  10. Close Commerce Server Manager.
  11. Click Start, click Run, type IISRESET, and then click OK.

STATUS

This behavior is by design. Because the char data type is not suitable for international characters, Commerce Server profiles were designed to use the nvarchar data type.

MORE INFORMATION

The char data type is a fixed-length data type when the NOT NULL clause is specified. If a value that is shorter than the width of the column is stored in a char NOT NULL column, SQL Server appends blanks to the value to make the length of the value match the width of the column. For example, if a column is defined as char(10), and the data to be stored is "music", SQL Server stores this data as "music_____".

Note In this example, an underscore (_) character represents a blank.

The ANSI_PADDING parameter affects a char NULL column as follows:
  • If the ANSI_PADDING parameter is set to ON when you create a char NULL column, the char NULL column behaves just like a char NOT NULL column. SQL Server appends blanks to the value to make the length of the value match the width of the column.
  • If the ANSI_PADDING parameter is set to OFF when you create a char NULL column, SQL Server truncates trailing blanks from character values that are stored in the column.
The nvarchar data type is a variable-length data type. SQL Server does not append blanks to values that are shorter than the width of the column.

The ANSI_PADDING parameter affects a nvarchar NULL column as follows:
  • If the ANSI_PADDING parameter is set to ON when you create a nvarchar NULL column, SQL Server does not truncate trailing blanks.
  • If the ANSI_PADDING parameter is set to OFF when you create a nvarchar NULL column, SQL Server truncates trailing blanks from character values that are stored in the column.
Modification Type:MajorLast Reviewed:7/20/2004
Keywords:kbprb KB838570 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.