Category Archives: Sitecore Commerce Server

Sitecore Commerce 8 powered by Microsoft Dynamics (SCpbMD) – No catalog data

In this blog post, I am going to share some SQL statements that will help save your sanity whilst working with Microsoft Ax integration.

Problem

The Customer and their Microsoft Ax partner assured me that the catalog data was published correctly to the channel database. Unfortunately, when the “Routing Service” was run – no data was imported into Sitecore Commerce Server and no error or exceptions were thrown. The import XML files where created correctly, just with no catalog content.

A big thanks to Sitecore (Canada) as we investigated the issue together, and they supplied a number of SQL statements, which reviled the truth.

Solution

Lets jump straight to the SQL statement that saved my sanity. Firstly you need the channel id for the SQL statements, see my previous blog post about how to get this.

The following SQL statement returns relevant catalog information for a given channel id.

exec sp_executesql N'SELECT         
                    getCatalogsFn.[CATALOG],
                    getCatalogsFn.[NAME],
                    getCatalogsFn.[DESCRIPTION],
                    getCatalogsFn.[IMAGE],
                    getCatalogsFn.[LANGUAGE],
                    getCatalogsFn.[ENABLESNAPSHOT],
                    getCatalogsFn.[VALIDFROM],
                    getCatalogsFn.[VALIDTO],
                    getCatalogsFn.[CREATEDDATETIME],
                    getCatalogsFn.[MODIFIEDDATETIME],
                    getCatalogsFn.[PUBLISHEDDATETIME]
                FROM crt.GETCATALOGSPUBLISHEDTOACTIVECHANNEL(@bi_ChannelId) getCatalogsFn',N'@dt_ChannelDate datetime,@bi_ChannelId bigint',@dt_ChannelDate='2015-10-14 00:00:00',@bi_ChannelId=[YOUR CHANNEL ID]

In my case when I ran the SQL statement above I found 2 issues. The first is that the catalog was not valid for the current date (see the image  below). Something was not setup correctly within Microsoft Ax as the valid from and to date were set to the .net minimum value 😦

date valid

The second issue was that the languages did not match. The channel language is “en-gb”, but the catalog language is “da”! Take a look at the image below.

In addition the language needs to be a fully qualified with culture so da should be da-dk.

language mis match

SQL statement to get the channel language

Another common error a mismatch in the channel/catalog languages, use the following SQL statement to find the channel language.

exec sp_executesql N'SELECT * 
   FROM crt.CHANNELLANGUAGESVIEW  
   WHERE CHANNEL = @channelId ORDER BY LANGUAGEID ',N'@channelId bigint',@channelId=[YOUR CHANNEL ID]

Well I hope this will be of some help, Alan

Sitecore Commerce 8 powered by Microsoft Dynamics (SCpbMD) – Find the channel ID

Sitecore expects an integer value to identify the channel id, which is used to identify the online store to retrieve from the channel database.

The problem I faced was that the neither the Microsoft Ax Partner or the customer could give me the id, all they could find was the name which was a string value!

But fear not the following SQL statement which you can run against the channel database will show you all the channels and their assoicated id 🙂

SELECT TOP 1000 [NAME]
      ,[CHANNELID]
      ,[OPERATINGUNITNUMBER]
      ,[SERVER]
      ,[DATABASE]
      ,[ISPUBLISHED]
      ,[ISLOCAL]
      ,[CONNECTIONSTRING]
  FROM [AsyncClientDB].[crt].[STORAGELOOKUPVIEW]

Also ensure that channel is in fact published otherwise Sitecore can not use it, hope this helps Alan

channel id