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.