Microsoft KB Archive/833555

= BizDesk does not validate the ProductID setting and the VariantID setting =

Article ID: 833555

Article Last Modified on 4/29/2007

-

APPLIES TO


 * Microsoft Commerce Server 2002 Service Pack 2

-



SUMMARY
''This article describes a problem that occurs when you incorrectly configure the product identifier (ProductID) and the variant identifier (VariantID) when you create a new catalog in Microsoft Commerce Server 2002. You may receive a 0x8898005F scripting error. This article includes a method to resolve the problem.''



SYMPTOMS
When you create a new catalog in Microsoft Commerce Server 2000 Business Desk (BizDesk), you cannot see if the product identifier (ProductID) and the variant identifier (VariantID) contain invalid characters or if they have the same value. Because you cannot see if they contain invalid characters or if they have the same value, problems may occur in the Microsoft Commerce Data Warehouse months after you create the catalog. You may receive the following error message:

Script Error:

Number: 0x8898005F

Description: The Product Identifier you specified contains a Variant Property that is being used as a ProductId for this catalog.

Source: Commerce.CatalogManager



CAUSE
The problems may occur if the ProductID property and the VariantID property contain characters that are not valid or if both properties have the same value.



RESOLUTION
To resolve this problem, do not use the same value for the ProductID property and the VariantID property. For more information, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms915345.aspx

If you have to use the same value for the ProductID property and the VariantID property, follow these steps:  Create a property to use as the VariantID for the new catalog in BizDesk.

Note If the datatype of the property is string, make sure that its maxlength is less than or equal to 256. By default, BizDesk creates a property that has a maxlength of 4000. Those properties cannot be used as a VariantID for the catalog. To change the VariantID for the catalog, run the following script in the catalog database to create the ctlg_ChangeCatalogVariantId procedure. To do this, follow these steps:  Open Microsoft SQL Query Analyzer. Connect to the computer that is running SQL Server. In the Current Database list, click the database that contains the catalog (BlankSite_Commerce, for example).  Copy and paste the following code, and then click the green arrow on the toolbar to run the code: /* This script changes the VariantID for the catalog to the specified VariantID property. Input Parameters: @CatalogName is the catalog name. @VariantIdProperty is the new VariantID property.

This script performs the following validations: 1. The input parameters cannot be blank. 2. The specified catalog should exist in the catalog system. 3. The specified variantid property should not be the same as the current ProductID property or the VariantID properties. 4. The specified variantid property should exist in the catalog system. 5. The specified variantid property should not be a multilingual property. 6. The specified variantid property should not have a datatype that is not valid. If the datatype is a string property, its maxlength should be less than or equal to 256. 7. The specified variantid property does not already exist in the catalog.

This script performs the following actions: 1. Updates the CatalogGlobal table. 2. Adds the VariantID property as a column to the catalog products table. 3. Updates the ProductID value and the VariantID value in the catalog products table. 4. Refreshes all the language specific views in the catalog.

Return Values 0 - The operation succeeded 1 - The operation failed

EXEC ctlg_DropCatalogObjectFromDatabase 'ctlg_ChangeCatalogVariantId' GO CREATE PROCEDURE dbo.ctlg_ChangeCatalogVariantId (   @CatalogName nvarchar(100),    @VariantIdProperty nvarchar(100) ) AS BEGIN SET NOCOUNT ON   DECLARE @VariantIdProperty_tmp nvarchar(100) DECLARE @ProductIdProperty_tmp nvarchar(100) DECLARE @CatalogTableName_tmp sysname DECLARE @CatalogView_tmp sysname DECLARE @Query_tmp nvarchar(4000) DECLARE @Languagesensitive_tmp bit DECLARE @VariantIdDataType_tmp smallint DECLARE @VariantIdMaxLength_tmp int

SET @CatalogName = LTRIM(RTRIM(@CatalogName)) SET @VariantIdProperty = LTRIM(RTRIM(@VariantIdProperty)) IF LEN(@CatalogName) <= 0 OR LEN(@VariantIdProperty) <= 0 BEGIN PRINT 'The @CatalogName and @VariantIdProperty should be specified' RETURN END

SELECT @VariantIdProperty_tmp = VariantId, @ProductIdProperty_tmp = ProductId FROM dbo.CatalogGlobal Where CatalogName = @CatalogName IF @VariantIdProperty_tmp IS NULL BEGIN PRINT 'CatalogName does not exist' RETURN 1 END IF @VariantIdProperty_tmp = @VariantIdProperty BEGIN PRINT 'Specified VariantId property is the same as the current VariantId property of the catalog' RETURN 1 END IF @ProductIdProperty_tmp = @VariantIdProperty BEGIN PRINT 'Specified VariantId property is the same as the current productid property of the catalog' RETURN 1 END

SELECT @Languagesensitive_tmp = LanguageSensitive, @VariantIdDataType_tmp = DataType, @VariantIdMaxLength_tmp = ISNULL(MaxLength, 4000) FROM  dbo.CatalogAttributes WHERE PropertyName =  @VariantIdProperty IF @@rowcount=0 BEGIN PRINT 'The specified VariantId property does not exist in the catalog system. You must first create the property.' RETURN 1 END SET @CatalogTableName_tmp = N'dbo.['+@CatalogName+N'_CatalogProducts]' IF EXISTS (Select '*'   From syscolumns    Where id = object_id(@CatalogTableName_tmp)    AND name = @VariantIdProperty) BEGIN PRINT 'The VariantId property already exists in the catalog. Please choose another property.' RETURN 1 END

IF @Languagesensitive_tmp = 1 BEGIN -- VariantID cannot be language sensitive PRINT 'The VariantId property cannot be language sensitive' RETURN 1 END IF (@VariantIdDataType_tmp <> 0) AND (@VariantIdDataType_tmp <> 1) AND (@VariantIdDataType_tmp <> 3) AND (@VariantIdDataType_tmp <> 5) BEGIN -- Invalid datatype for VariantID PRINT 'The property cannot be used as a Variantid because its data type is not one of int, bigint, string or float. '       RETURN 1 END

IF (@VariantIdDataType_tmp = 5 AND @VariantIdMaxLength_tmp > 256) BEGIN -- Invalid maxlength for VariantID PRINT 'The property cannot be used as a Variantid because it is a string property and its max length exceeds 256 characters. '       RETURN 1 END -- Update the VariantID column in the CatalogGlobal table. UPDATE dbo.CatalogGlobal SET VariantId = @VariantIdProperty Where CatalogName = @CatalogName -- Add the VariantID property as a column to the CatalogProducts table. SET @Query_tmp = N'ALTER TABLE '+@CatalogTableName_tmp+N' ADD ['+@VariantIdProperty+N'] ' +dbo.GetSQLDataType(@VariantIdDataType_tmp,@VariantIdMaxLength_tmp)+' NULL' EXEC (@Query_tmp) IF @@error <> 0 BEGIN RETURN 1 END

-- Update the ProductID value and the VariantID value in the CatalogProducts table. SET @Query_tmp = N' UPDATE '+@CatalogTableName_tmp+N' SET ['+@VariantIdProperty+N'] = VariantId,[' +@ProductIdProperty_tmp+N'] = ProductId ' EXEC (@Query_tmp) IF @@error <> 0 BEGIN RETURN 1 END -- Update all the language views for the catalog because a new column has been added to the CatalogProducts table. DECLARE @CatalogLanguages TABLE (       language nvarchar(11)    ) INSERT @CatalogLanguages(language) VALUES('LNG_NEUTRAL') INSERT @CatalogLanguages(language) Select Language From CatalogLanguage Where CatalogName = @CatalogName SET @Query_tmp = N''

SELECT @Query_tmp = @Query_tmp+N' EXEC sp_refreshview ['+@CatalogName+N'_'+language+N']' FROM @CatalogLanguages EXEC (@Query_tmp) IF @@error <> 0 BEGIN RETURN 1 END -- Return success RETURN 0 END </li></ol> </li> To run the following code, follow these steps: <ol style="list-style-type: lower-alpha;"> Open SQL Query Analyzer.</li> Connect to the computer that is running SQL Server.</li> In the Current Database list, click the database that contains the catalog (BlankSite_Commerce, for example).</li>  Copy and paste the following code, and then click the green arrow on the toolbar to run the code: DECLARE @CatalogName nvarchar(100) DECLARE @VariantIdProperty nvarchar(100) DECLARE @ReturnValue smallint

-- Set the correct values for the two input parameters. SET @CatalogName = N' Your catalog name ' SET @VariantIdProperty = N' The new VariantID property for the catalog '

-- Execute the procedure as a part of a transaction. BEGIN TRAN EXEC @ReturnValue = dbo.ctlg_ChangeCatalogVariantId @CatalogName, @VariantIdProperty

-- If the operation failed, rollback the transaction. IF @ReturnValue <> 0 BEGIN Print 'The operation failed' ROLLBACK TRAN END ELSE -- Commit the transaction. BEGIN Print 'The operation succeeded' COMMIT TRAN END </li></ol> </li> Run the script in the catalog database.

If the operation is not successful, SQL Query Analyzer displays the reason for the failure.</li> Repair any problems that occur.</li> Rebuild all the virtual catalogs from BizDesk.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

Steps to reproduce the problem

 * 1) Unpup the retail site.
 * 2) Create a new base catalog in BizDesk.
 * 3) Set the values for the following fields:
 * 4) * Name: Test
 * 5) * Currency: US Dollar
 * 6) * Product unique ID: ISBN
 * 7) * Product variant unique ID: ISBN
 * 8) Click Save, and then click Back.
 * 9) Click Open.
 * 10) Click New.
 * 11) Click Product Definition: Book, and then click OK.
 * 12) Set the values for the following fields:
 * 13) * ISBN: 1234567890
 * 14) * Name: My book
 * 15) Click Save.

Note You may receive the error message that is mentioned in the &quot;Symptoms&quot; section. However, you may not receive the error message if you did not populate the catalog by using BizDesk.

Keywords: kbprb KB833555

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.