Aborting import operations in Commerce Server 2007

Today, my Biztalk ran wild and initiated a bunch of Commerce Server catalog import operations. While trying to find a way to abort all of those I came across the CatalogStatus table in the product catalog database.

According to MSDN this table contains the status of operations in the catalog system. Among other things you can monitor the PercentComplete column for operation progress.

Turns out you can also abort any operation by setting AbortRequested to 1, so the SQL below did the work for me.

UPDATE CatalogStatus
SET AbortRequested = 1
WHERE StartDate =  GETDATE()-1

Field not found: CatalogResourceMajorVersion from Commerce Server 2007

So I was trying to troubleshoot this Biztalk 2006 R2 orchestration that includes importing some data into a Commerce Server 2007 instance. CS doesn’t really want to play ball, so I decide to upgrade it to the latest service pack as that’s supposed to resolve some of the issues. Download SP2, install, migrate, restart IIS… BOOM! Exception:
MissingFieldException: Field not found: 'Microsoft.CommerceServer.Catalog.Internal.Constants.CatalogResourceMajorVersion'.]
Microsoft.CommerceServer.Catalog.CatalogContext.InternalCreate(CatalogSiteAgent catalogSiteAgent, DebugContext debugContext, CacheConfiguration cacheConfiguration) +708
Microsoft.CommerceServer.Runtime.Catalog.CommerceCatalogModule.CreateCatalogContext() +293
Microsoft.CommerceServer.Runtime.Catalog.CommerceCatalogModule.get_CatalogContext() +138
Microsoft.CommerceServer.Runtime.Catalog.CommerceCatalogModule.OnBeginRequest(Object sender, EventArgs e) +24
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +80
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +171

Google is, for once, silent… So I check the GAC and compare the version Microsoft.CommerceServer.Catalog.dll to the other CS dlls – bingo, the version of the dll is 6.0.3724.0. A quick search leads you to this MSDN blog post which tells you which version corresponds to which SP…

Apparently the dll failed to register during the sp2 install which led to the weirdness described above.

As I couldn’t find the right dll anywhere and was not able to reinstall sp2 I had to install sp1 and THEN sp2. This finally took care of the problem.

Listing subcategories for a category in Commerce Server 2007 using a SQL query

As promised, here is the follow-up to the last post. This query gets us the names of all subcategories of a particular category in Commerce Server 2007.

DECLARE @categoryOid AS INT
SET @categoryOid = 937 ;

WITH    Cats2 ( oid, child_oid, hierarchy_level )
          AS ( SELECT   oid ,
                        child_oid ,
                        1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy
               WHERE    oid = @categoryOid
                        AND CatalogName = 'ConsumerStaging'
               UNION ALL
               SELECT   h.oid ,
                        h.child_oid ,
                        c.hierarchy_level + 1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy h
                        JOIN Cats2 c ON h.oid = c.child_oid
                                        AND h.CatalogName = 'ConsumerStaging'
    SELECT  CategoryName
    FROM    dbo.ConsumerShop_CatalogProducts
    WHERE   oid IN ( SELECT DISTINCT ( oid ) 
					 FROM   Cats2 )

Listing categories for a product in Commerce Server 2007 using a SQL query

Commerce Server API is not exactly known for being lightning-fast, so sometimes you want to skip it altogether and just use plain old SQL to get the data.

This time I needed to find all categories (at a particular level) that a product belongs to. This was solved by the script below, which is using a recursive CTE to walk upwards through the tree.

Just set the @productId and @hierarchyLevel below and voilá!

DECLARE @hierarchyLevel INT
DECLARE @productId VARCHAR(10)
SET @hierarchyLevel = 3
SET @productId = '25853' ;

WITH Cats (oid, child_oid, hierarchy_level) AS
		SELECT h.oid, h.child_oid, 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h 
		JOIN dbo.ConsumerShop_CatalogProducts p ON h.child_oid = p.oid
		WHERE p.ProductID = @productId
		AND h.CatalogName = 'ConsumerStaging'


		SELECT h.oid, h.child_oid, c.hierarchy_level + 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h
		JOIN Cats c ON c.oid = h.child_oid
		WHERE h.CatalogName = 'ConsumerStaging'
    SELECT  CategoryName
    FROM    Cats
            JOIN dbo.ConsumerShop_CatalogProducts p ON Cats.oid = p.oid
    WHERE   hierarchy_level = @hierarchyLevel
            AND BaseCatalogName = 'ConsumerStaging'

In my next post I will explain how to accomplish the opposite, i.e. find all subcategories of a category.

Debugging Commerce Server pipelines

To get debug information from Commerce Server pipelines you should:

Add loggingEnabled=”true” to the pipelines section in web.config

       <pipeline name="basket"
                 loggingEnabled="true" />

Next, set the “_Trace_Discounts” key in the pipeline context dictionary (or PipelineInfo[“_trace_discounts”] = 1;) before executing the basket pipeline. If you are using the StarterSite, you’ll need to edit the code for CommerceComponents.dll.

In ControlLibraryHelpersBasketHelper.cs the RunPipeline() function:

    private PipelineExecutionResult RunPipeline(string pipelineName, OrderPipelineType type)
        // [snip]
        using (PipelineInfo pipeline = new PipelineInfo(pipelineName, type))
            pipeline["_trace_discounts"] = 1;
            // [snip]
            return basket.RunPipeline(pipeline);

The log files are generated in the log subfolder from your pipeline definitions.