BizDesk does not validate the ProductID setting and the VariantID setting (833555)


The information in this article applies to:

  • Microsoft Commerce Server 2002 SP2

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://msdn.microsoft.com/library/default.asp?url=/library/en-us/csvr2002/htm/cs_bd_catalogs_zwfo.asp

If you have to use the same value for the ProductID property and the VariantID property, follow these steps:
  1. 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.
  2. 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:
    1. Open Microsoft SQL Query Analyzer.
    2. Connect to the computer that is running SQL Server.
    3. In the Current Database list, click the database that contains the catalog (BlankSite_Commerce, for example).
    4. 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
  3. To run the following code, follow these steps:
    1. Open SQL Query Analyzer.
    2. Connect to the computer that is running SQL Server.
    3. In the Current Database list, click the database that contains the catalog (BlankSite_Commerce, for example).
    4. 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
  4. Run the script in the catalog database.

    If the operation is not successful, SQL Query Analyzer displays the reason for the failure.
  5. Repair any problems that occur.
  6. Rebuild all the virtual catalogs from BizDesk.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

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:
    • Name: Test
    • Currency: US Dollar
    • Product unique ID: ISBN
    • Product variant unique ID: ISBN
  4. Click Save, and then click Back.
  5. Click Open.
  6. Click New.
  7. Click Product Definition: Book, and then click OK.
  8. Set the values for the following fields:
    • ISBN: 1234567890
    • Name: My book
  9. Click Save.
Note You may receive the error message that is mentioned in the "Symptoms" section. However, you may not receive the error message if you did not populate the catalog by using BizDesk.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB833555 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.