Controlling Duplicate Detection

The CRM SDK messages CreateRequest and UpdateRequest support a configuration parameter "SuppressDuplicateDetection" that provides control over whether duplicate detection rules will be applied - see http://msdn.microsoft.com/en-us/library/hh210213(v=crm.6).aspx. However, this parameter is not available through over programmatic means (such as the REST endpoint) to create or update records.

To workaround this, I created a plugin that sets the "SuppressDuplicateDetection" parameter based on the value of a boolean attribute that can included in the Entity instance that is created or updated.

I've posted the source code to the MSDN Code Gallery here

I created this because I had a need to apply duplicate detection rules to entities created via the REST endpoint in CRM 2011.

It may be that this plugin could also be used as a way to revert the CRM 2013 behaviour back to that of CRM 2011, to allow duplicate detection rules to fire on CRM forms. However, I've yet to test this fully; if anybody wants to test it, feel free to do so and make comments on this post. Otherwise, I'll probably update this post if I find anything useful with the CRM 2013 interface.

Crm 2013 – Script errors after upgrading an ex-Crm 4.0 organisation

After a recent upgrade to Crm 2013 of an organisation that had been a Crm 4.0 organisation, there were client script errors when navigating to the Case or Queue entities. The underlying cause was some SiteMap entries that referenced Crm 4.0 urls; these were being redirected to new urls, but seemed to be missing some elements on the QueryString.

The SiteMap entries with issues were:

<SubArea Id="nav_cases" Entity="incident" DescriptionResourceId="Cases_SubArea_Description" Url="/CS/home_cases.aspx" />
<SubArea Id="nav_queues" Entity="queue" Url="/workplace/home_workplace.aspx" DescriptionResourceId="Queues_SubArea_Description">
  <Privilege Entity="activitypointer" Privilege="Read" />
</SubArea>

The fix is to replace them with the following (which come from a default SiteMap in a new Crm 2013 organisation, though I’ve stripped out the GetStarted attributes):

<SubArea Id="nav_cases" DescriptionResourceId="Cases_SubArea_Description" Entity="incident" />
<SubArea Id="nav_queues" ResourceId="Homepage_Queues" DescriptionResourceId="Queues_SubArea_Description" Icon="/_imgs/ico_18_2020.gif" Url="/_root/homepage.aspx?etc=2029" >
 <Privilege Entity="queue" Privilege="Read" />
</SubArea>

These are the only entries I’ve found so far with problems. I think the entry for Queues is a one-off, but the entry for cases is notable in that the original (Crm 4.0) SiteMap entry included a Url attribute, whereas entries for most other entities did not include the Url attribute. So, it’s possible that other entries that include both the Entity and Url attribute could have the same issue.
Although annoying at the time, I don’t see this as a major issue, as reviewing the SiteMap will be one of the standard tasks we do for any upgrades to Crm 2013. This is due to change in navigation layout, which means the overall navigation structure deserves a rethink to make best use of the new layout. When doing this, we find it is best to start with a new clean SiteMap and edit this to a customer-specific structure for Crm 2013, rather than trying to edit an existing structure. It’s also worth noting that a few of the default permissions have changed (spot the difference above for the privilege to see the Queues SubArea), and it’s worth paying attention to these at upgrade time for future consistency.

Crm 2013 – Upgrading from an ex-Crm 1.2 organisation

This post should only affect a small fraction of Crm 2013 users, but if you do have a CRM organisation that was first created in Crm 1.2, and upgraded through the versions to Crm 2013, you may get an “unexpected error” message when opening account contact or lead records that had been created in Crm 1.2 (I told you this wouldn’t affect many people, but we do still have, and interact with, customers from Crm 1.2 days).

The cause of this is the ‘merged’ attribute. Record merging (for accounts, contacts and leads) was introduced in Crm 3.0, and a ‘merged’ attribute was created to track if a record had been merged. For all records created in Crm 3.0 and higher, this attribute was set to false, but for records created in Crm 1.2, the attribute was null.

This causes a problem in the RTM build of Crm 2013. If you enable tracing, you will see an error like the following:

Crm Exception: Message: An unexpected error occurred., ErrorCode: -2147220970, InnerException: System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Crm.BusinessEntities.RecordDisabledMergedNotificationGenerator.BusinessLogic(IBusinessEntity entity, IOrganizationContext context, NotificationAdder notificationAdder)

So, that’s the problem. There are three ways to fix it:

• If you’ve already upgraded, then the quick, but unsupported, fix is via direct SQL statements that set the merged attribute to false (see below)
• If you have not yet upgraded, you can merge each affected record in turn with a dummy record, which will set the merged attribute.
• You can automate the merge process programmatically by submitting a merge request for each record, and passing appropriate parameters. I’m not sure if this will work after the upgrade, or only before, as I’ve not tried it

Unfortunately (but unsurprisingly), the merged attribute is not ValidForUpdate, so you can’t use a simple, supported update request to set the attribute

The SQL statements for an unsupported fix:

update contact set merged = 0 where merged is null

update account set merged = 0 where merged is null
update lead set merged = 0 where merged is null

Crm 2013 – No more ExtensionBase tables

So, Dynamics Crm 2013 is here, and there’s lots to say about the new UI, and the new features. But, many others are talking about these, so I thought I’d start with what may seem to be an obscure technical change, but it’s one that I welcome, and which is a significant contribution to the stability and performance of Crm 2013.

With Crm 3.0, Microsoft changed the underlying table structure so that any customisable entity was split across 2 tables; a base table that contained all system attributes, and an extensionbase table for custom attributes. For example, there was an accountbase and an accountextensionbase table. Each table used the entity’s key as the primary key, and the extensionbase table also had a foreign key constraint from the primary key field to the primary key in the base table. Each entity has a SQL view that joined the data from these table to make it appear as one table to the platform. As I understand it, the main reason for this design was to allow for more custom attributes, as SQL Server had a row-size limit of 8060 bytes, and some of the system attributes were already using ~6000 bytes.

The same table design was retained in Crm 4.0 and Crm 2011. However, Crm 2011 introduced a significant change to the plugin execution pipeline, which allowed custom plugins to execute within the original SQL transaction. This was a very welcome change that provided greater extensibility. However it did mean that the duration of SQL transactions could be extended, which means that SQL locks may be held for longer, which means potentially more locking contention between transactions. In very occasional circumstances, a combination of certain plugin patterns, the design of the base and extensionbase tables, and heavy concurrent use, could give rise to deadlocks (see below for an example).

Given this, I’m very glad that the product team retained the facility to have plugins execute within the original transaction (then again, it would be hard to remove this facility from us). It wouldn’t be realistic to ask customers to reduce concurrent usage of CRM, so the only way to reduce the potential deadlock issue was to address the design of the base and extensionbase tables. From my investigations (sorry, but I actually quite like investigating SQL locking behaviour), a substantial improvement could have been made by retaining the table design, but modifying the SQL view, but a greater improvement comes from combining the tables into one. An added advantage of this change is that the performance of most data update operations are also improved.

Deadlock example

Here are two SQL statements generated by CRM:

select
'new_entity0'.new_entityId as 'new_entityid'
, 'new_entity0'.OwningBusinessUnit as 'owningbusinessunit'
, 'new_entity0'.OwnerId as 'ownerid'
, 'new_entity0'.OwnerIdType as 'owneridtype'
from new_entity as 'new_entity0'
where ('new_entity0'.new_entityId = @new_entityId0)  

And

update [new_entityExtensionBase]

set [new_attribute]=@attribute0
where ([new_entityId] = @new_entityId1)
 
These were deadlocked, with the SELECT statement being the deadlock victim. The locks that caused the deadlock were:

• The SELECT statement had a shared lock on the new_entityExtensionBase table, and was requesting a shared lock on new_entityBase table
• The UPDATE statement had an update lock on the new_entityBase table, and was requesting an update lock on new_entityExtensionBase table

The likely reason for this locking behaviour was that:

• Although the SELECT statement was requesting fields from the new_entityBase table, it had obtained a lock on the new_entityExtensionBase table to perform the join in the new_entity view
• The UPDATE statement that updates a custom attribute (new_attribute) on the new_entity entity would have been the second statement of 2 in the transaction. The first statement would modify system fields (e.g. modifiedon) in the new_entityBase table, and hence place an exclusive lock on a row in the new_entityBase table, and the second statement is the one above, which is attempting to update the new_entityExtensionBase table

Both operations needed to access both tables, and if you’re very unlucky, then the two operations, working on the same record, may overlap in time, and cause a deadlock.

The new design in Crm 2013 solves this in three ways:

1. With just the one entity table, the SELECT statement only needs one lock, and does not need to obtain one lock, then request another
2. Only one UPDATE statement is required in the transaction, so locks are only required on the one table and they can be requested together, as they would be part of just one statement
3. Both operations will complete more quickly, reducing the time for which the locks are held

Of these 3 improvements, either no. 1 or 2 would have been sufficient to prevent deadlocks in this example, but it is gratifying that both improvements have been made. The third improvement would not necessarily prevent deadlocks, but will reduce their probability by reducing overall lock contention, and will also provide a performance improvement.