Tag Archives: Microsoft Dynamics

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

1 Reply

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) – Cannot change the publishing status of the channel

1 Reply

Well I have been having a lot of fun setting up Sitecore Commerce 8 powered by Microsoft Dynamics; there are a lot of moving parts to get get the catalog data from Ax, all the way into Sitecore. Not to get political but in fact 90% of the issue have come from Microsoft Ax not being setup correctly.

Routing Service

The Routing Service is responsible for transferring the catalog data from the Channel Database into Sitecore Commerce Server which acts as an edge server for Sitecore. In addition the routing service needs to access the Microsoft Ax Real Time Service (RTS) to change the publishing status.

But why does Sitecore need to update the publishing status anyway? I would assume that was the responsibility Microsoft Ax?

The Sitecore Routing Service is considered part of AX channel publish step. The Routing Service needs to ensure all publishes from HQ to channel database is finished. For a more detailed answer see this article from Microsoft, where the “Routing Service” is equivalent to the SharePoint in the diagram.

I found the following articles by Hao Liu, very useful in tracking down errors and issues with the catalog synchronization.

Communication with Ax is a black box

When communicating with Microsoft Ax it is completely transparent. You call the Microsoft run time DLL’s which connect to the channel database; which resolves how to communicate with the RTS, get the catalog data, create shopping baskets, talk to HQ’s Ax to create orders etc.
Which is great until it does not work as determining where it has gone wrong is a nightmare.
I did not have access to the Microsoft Ax setup and we got  the following exception.

Exception while calling invoke method UpdateChannelPublishingStatus: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail.

Any of the following reasons can cause the a fore mentioned error

  1. You have to use SSL to communicate with the RTS web services
  2. The RTS web service is not running at all.
  3. The RTS web service is not running SSL (which it must)
  4. The RTS web service “host name” does not match the name of the SSL certificate.
  5. The host name running the RTS Web service does not match the host defined in Microsoft Ax.
  6. The credentials running the RTS web service are not correct, and do not have access to the HQ Ax.

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.

Then we got the following exception and we (Sitecore Support, The Microsoft Ax Partner & myself) spent a lot of time trying to identify what was causing the following exception:

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.
at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.GetResponseFromMethod(String methodName, Object[] parameterList, Boolean useExtensionMethod) at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.UpdateChannelPublishingStatus(Int64 channelId, OnlineChannelPublishStatusType publishingStatus, String publishingStatusMessage)

Solution

I am not sure if it was sheer desperation, divine intervention, or more likely luck!!!

But I checked the SQL server which hosted the channel database, and it had no disk left.

For some reason (I did not install the async client and or the channel database) the recovery mode was set to “Full” and it had therefore create 1 TB of log file 😦

I set the recovery mode to “Simple”, ran shrink on the database and the size dropped from 1TB to almost nothing, and the exception was gone 🙂