Tag Archives: Sitecore

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

How to suspend sitecore schedule publishing – aborting the publish pipeline is not enough, it requires an exception!

The customer wanted the ability to suspend scheduled publishing, but could still make manual publishes (i.e. started from the Sitecore client).

Each time a publish is started it runs the publish pipeline. Therefore it is possible to insert a custom pipeline step at the beginning (see below) to do the following:

  1. Identify if it was a scheduled publish
  2. Check if a check-box in Sitecore is ticked
  3. If both conditions are met – abort the publish pipeline to stop the publish

publish pipeline

Unfortunately aborting the publish pipeline is not enough 😦

In the initial code I would abort the pipeline using AbortPipeline() (see below) as I assumed this was enough to stop the publish. The pipeline was aborted and no items were published, but the code that starts the pipeline still updated the properties table indicating that the publish had succeeded:-(

code

Side affect

This had the side effect that when the schedule publishing was enabled again, any items that were modified or created whist the publishing was disabled would not be published as when scheduled publishing was resumed Sitecore believed that they had already been published.

Solution

After checking the code using reflector I determined if I threw an exception, it would ensure that the properties table was not updated. So the publish was completely cancelled, and when scheduled incremental publishing was resumed it will publish all the items that have been modified since the last successful publish, and not since the last aborted publish.

How to identify a scheduled publish

Not the nicest solution but it works! I check the publish context user which can have the following values:

  1. The user logged into sitecore – If publish is started from the Sitecore client
  2. sitecore\Anonymous – if the publish is started by the scheduler

If the value is sitecore\Anonymous I know that it is a scheduled publish.

is schedules

 

 

 

How to filter AD roles or users using Sitecore’s LDAP module

Problem
Every time I have used AD for providing access to Sitecore, the active directory (AD) structure is crazy and recently I had a customer that had over 18000 roles, which made it difficult to assign roles and it killed the performance of the Sitecore client, as each user had at least 500 roles. Therefore Sitecore to evaluate the combination of a lot of roles to determine if they had read access or not.
18000I talked to the department responsible for the AD setup about changing and or creating a folder that only contained the Sitecore related roles, but this was not possible.

Initially I thought I would have to make own LDAP provider which derives from the standard provider, but I discovered this was not necessary as the LDAP module provides the functionality as standard.

Custom Filters
Custom Filter provide the ability to filter the roles and or users returned from the AD (see section 4.1 for full documentation).The custom filter uses the standard LDAP query syntax (see MSDN) to specify how the user or roles are filtered.

The following example ensures only roles, which contain Sitecore and or the special operations role; are imported into Sitecore. The Customcustom Filter

According to Sitecore documentation, both the User and Role provider must have the same CustomFilter, and that is why the (objectCategory=person) is added so all users are also imported regardless of their name.

I hope this blog post will help others using LDAP to control what roles or users are shown within sitecore.

Sitecore SPEAK Insert Link Dialog – missing icons & sitecore tree – Don’t panic, U didn’t break it!!!!

I was upgrading a solution from 6.2 to 7.2 and it had a number of custom fields that no longer worked,  so I started to fix the code.

One of the custom fields showed the SPEAK  Insert link dialog and after making a lot of changes, I noticed that I had broken the SPEAK dialog, see below

Speak dialog....

I reviewed the changes I had made, and could not see what could have caused this issue? But to be on the safe side, I undid all of my changes, did a full build, etc and still I had the issue 😦

So I tried to open the dialog from a sitecore standard field, and it was also broken 😦

Solution

Whist starting to panic, after 5 minutes I noticed that the dialog had fixed itself!!!!

It appears that after a restart of the website the first time you open the “Insert link” dialog you have to wait 5 minutes for it to start working, after that it works straight away.

 

 

 

Sitecore 7 – Disable indexing – It is not enough to set Indexing.UpdateInterval to 00:00:00.

I was working on a setup for a customer where they have a dedicated publish server and therefore it was not necessary to update the indexes. There are many post stating that to disable sitecore indexing you have to set the Indexing.UpdateInterval setting to 00:00:00 i.e.

<setting name="Indexing.UpdateInterval" value="00:00:00"/>

The website has been recently upgraded from 6.6 to 7.5 and I noticed that the indexes were still being built?

So I used Brian’s jobs page (see his blog) which listed all the running jobs to see what was running and to my shock there were a lot of index update jobs? Which meant that instead of using all the machines power to publish it was busy indexing ALL the time?

running jobs

But since Sitecore 7.0 there is an extra setting which has to be changed in order to disable indexing.

Solution

To disable indexing the BucketConfiguration.ItemBucketsEnabled setting also must be set to false in the Sitecore.Buckets.config i.e.

<setting name="BucketConfiguration.ItemBucketsEnabled" value="false"/>

Hope this helps 🙂

Missing dialog’s after upgrading to Sitecore 7.5

After upgrading to Sitecore 7.5 there where a number of SPEAK dialog’s (select media dialog, insert item link, etc) that would open and show the front page of the website?

I eventually tracked it down to they fact that their where a number of duplicated SPEAK items in the core database see the image below and the fact that the solution had some custom code which showed the front page if an item has no renderings defined (not a good idea).

items

 

I believe that whist upgrading a update package failed and or did not remove the unused items.

Solution

Delete all the items that have the folder icon, and then all the dialog should work again, of course take a backup of the items before deleting them.