Navigation


Upcoming courses:

  • Aarhus, Denmark, March 5 - 9, 2012
  • New York City, USA, March 26 - 30, 2012
Read more on our website

About me

Brian Holmgård Kristensen

Hi, I'm Brian. I'm a Danish guy primarily working with ASP.NET e-commerce solutions using Microsoft Commerce Server.

I'm co-founder and core-member of Aarhus .NET Usergroup (ANUG), which is a offline community for .NET developers in Denmark.

You can visit my View Brian Holmgård Kristensen's profile on LinkedIn or follow me on Twitter @brianh_dk. Also please feel free to contact me via e-mail Send me an e-mail.

Categories

On this page

Archive

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

RSS 2.0

Send mail to the author(s) E-mail

Total Posts: 36
This Year: 0
This Month: 0
This Week: 0
Comments: 10

Sign In

Follow me on Twitter @brianh_dk
 Saturday, 25 August 2007

Short explanation

When creating a new property definition which has the 'Assign to all products' attribute set to true and when this new property is added to the propertiesToReturn element in web.config (configuration/commerceSite/propertiesToReturn) search operations on the web-site can cause the following exception: Invalid column name '[property-name]'. This occurs if one or more of the catalogs in Commerce Server are out of sync with the catalog schema (defined in the Catalog And Inventory Schema Manager) e.g. if the catalog is empty.

Solution: Create a new dummy product in each catalog to ensure synchronization is done. You can safely delete the dummy product after creation.


Complete explanation

I had added the property 'Show_Variants_As_Products' in the Catalog And Inventory Schema Manager. The 'Assign to all products' attribute was set to true to ensure that all products in Commerce Server include this property.

Afterwards I added the new property to the propertiesToReturn string list in web.config:

<commerceSite
 requireSSL="false"
 enableExpressCheckout="false"
 persistAnonymousBaskets="false"
 rewriteProductUrls="true"
 assetLocation="~/"
 defaultShippingMethodId="87c0e40f-3661-4197-8f88-fa3f101ae1ad"
 propertiesToReturn="CategoryName, ProductID, CatalogName, i_ClassType, DisplayName, cy_list_price, VariantID,  Show_Variants_As_Products">

This setting ensures that the property is available in the returned DataSet when e.g. performing search operations on the web-site.

However when I tried to perform a search, the web-site crashed with the following exception coming from the SQL Server:

Invalid column name 'Show_Variants_As_Products'.

In the Catalog Manager I opened a random product in one of my catalogs to verify that the new property was actual present - and it was. So a bit confused I opened SQL Server Profiler to create a new trace to check the queries which was actually sent to the database from the web-site when performing a search.

The query sent to the database was:

exec dbo.ctlg_GetResults
 @Catalogs=N'MyCatalog1,MyCatalog2,MyCatalog3,',
 @Language=N'da-DK',
 @PropertiesToReturn=N
'[CategoryName], [ProductID], [CatalogName], [i_ClassType], [DisplayName], [cy_list_price], [VariantID], [Show_Variants_As_Products], [BaseCatalogName],
 [oid], [OrigProductId], [OrigVariantId], [OrigCategoryName], [DefinitionName], [PrimaryParentCategory], [UseCategoryPricing]'
,
 @SQLClause=NULL,
 @FTSPhrase=N'perfume',
 @AdvancedFTSPhrase=NULL,
 @OrderBy=NULL,
 @StartingRec=1,
 @NumRecords=10,
 @SortAscending=1,
 @ClassType=10,
 @eJoinType=-1,
 @TargetTableName=N'',
 @SourceJoinKey=N'',
 @TargetJoinKey=N'',
 @RecordCount=@p16 output,
 @EnableInventory=1,
 @InventoryServerName=NULL,
 @InventoryDatabaseName=NULL,
 @FilterOutOfStockProducts=0,
 @FilterBackOrderableProducts=0,
 @FilterPreOrderableProducts=0,
 @UseThresholdAsFloor=1,
 @TreatmissingAsOutOfStock=0,
 @StockHandling=0,
 @InventoryPropertiesToReturn=N'*',
 @CategoryClause=NULL,
 @Recursive=0

When executing this query directly on database I got the same error message. I opened each of the catalog tables to verify that the new column 'Show_Variants_As_Products' was present. The column was present in the first two catalogs (MyCatalog1 and MyCatalog2) but in the last catalog (MyCatalog3) the column was not present indicating that this specific table was out of sync with the Catalog schema. The catalog was also in fact empty. So creating a new dummy product in MyCatalog3 using the Catalog Manager did the trick of getting the MyCatalog3 catalog table back in sync with the Catalog schema. I tested it with a new search operation on the web-site and everything returned successfully.

Posted on Saturday, 25 August 2007 11:24:16 (Romance Standard Time, UTC+01:00)
# | Comments [0]
Comments are closed.