Sunday 14 December 2008

Invalid Argument error when importing unpublished entities

If, like I did recently, you export an entity before it has been published, you will get an 'Invalid Argument' error when importing the entity if it has any relationships.

The reason for this is that the relationship attribute will have no display name, which results in the error. The simplest solution would be to go back to the original deployment, publish the entity, then export again. But, if that's not an option, you can fix the problem within the exported xml as follows:

The invalid relationship xml will look like the following:

<field name="pjv_targetid" requiredlevel="required" imemode="auto" lookupstyle="single" lookupbrowse="0">
<displaynames />
</field>


The problem is that the displaynames element is blank. This can be changed to something like the following

<displaynames>
<displayname description="Target" languagecode="1033" />
</displaynames>


The languagecode attribute will need to be set to the correct value for your deployment. Many other elements will have this value set in the customizations.xml file

Monday 24 November 2008

More CRM MVPs

This a belated welcome to all recently awarded CRM MVPs. At the time of writing there are now 29 of us worldwide. I've updated the blog list to reflect the new MVPs - as far as I can tell these are up-to-date, but let me know if not

Wednesday 19 November 2008

Using the Reporting Services Execution Log with CRM 4.0

I've contributed another article to the Microsoft CRM Team Blog. This is about making good use of the Reporting Services Execution Log with CRM 4.0 reports.

In the past I've found the execution log to be very useful for getting statistics on when reports were run, and the overall performance. However, CRM 4.0 introduced some complexities around using the log effectively. One issue is that the report name is not stored in Reporting Services, and another issue is that, if you use the Reporting Services connector, then the user name is not recorded in the execution log. The article describes how to resolve these issues.

I've added some support files for this to the MSDN Code gallery

Monday 17 November 2008

Report Builder 2.0 for SQL 2008 - No need for a report model

I've always considered the Report Builder tool that ships with SQL Reporting Services to be a good tool for generating reports without SQL knowledge, and in the context of CRM it's much more powerful than the CRM 4.0 Report Wizard.

One drawback of the Report Builder for SQL 2005 was that it needed a Report Model to abstract the underlying data, and Report Models are not dynamic (see below for why this was a pain). One major advantage of SQL 2008 is that it ships with a new version of Report Builder, Report Builder 2.0, that allows direct connections to the SQL database. This will make it a lot more useful in CRM deployments, especially when your schema changes. A good overview of the use of Report Builder 2.0 can be found here.

Why Report Models are a pain with CRM
Earlier, I said 'Report Models are not dynamic' Put another way, if you built your Report Model on your CRM database, then added a new attribute to an entity, you would have to regenerate your Report Model. In itself this wouldn't be too bad, but when you generate a Report Model from a filtered view you end up with a lot of unnecessary attributes, and a fair bit of work to do to provide a sensible set of attributes in the Report Model. To get round this we wrote a set of tools to manipulate the Report Model files directly and automate a lot of the rebuilding. This saved a lot of time, but still required a deployment process to allow reports against new attributes or entities.

Wednesday 22 October 2008

Deploying Custom Workflow Activities - Add authorizedType

This is the first of an intended two posts about registering custom workflow activities, and regards the initial deployment; a subsequent post will address updates and versioning issues.

The CRM 4.0 SDK gives a reasonable overview of registering a custom workflow activity, covering the following steps:
  1. Building / Deploying a plugin registration tool
  2. Registering the assembly
  3. Adding referenced assemblies to the GAC
  4. Stopping and restarting the CRM Async Service

However, one important point is omitted - adding your activity classes as an authorizedType within the web.config file. Workflow in CRM 4.0 uses the Windows Workflow Foundation (WWF) which, as a relatively new .Net technology, includes a reasonable security model to reduce the risk of malicious code being deployed within it.

The security model in WWF will only allow a permitted list of classes to be called as custom activities. In CRM 4.0 this list is stored in the web.config file in the root of the CRM website, and looks like this:

<System.Workflow.ComponentModel.WorkflowCompiler>
<authorizedTypes>
<authorizedType Assembly="System.Workflow.Activities, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Workflow.Activities" TypeName="IfElseActivity" Authorized="True"/>
<authorizedType Assembly="System.Workflow.Activities, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Workflow.Activities" TypeName="IfElseBranchActivity" Authorized="True"/>
</authorizedTypes>
</System.Workflow.ComponentModel.WorkflowCompiler>

For your custom activity to be permitted, you have to add an entry as an element; for example:

<authorizedType Assembly="MyAssembly, Version=4.0.0.0, Culture=neutral, PublicKeyToken="7766554433221100" Namespace="MyActivities" TypeName="*" Authorized="True"/>

Note that I've used an asterisk (*) to indicate all classes in the given namespace and assembly are permitted; you could reference an individual class if you prefer. Also note that the assembly is referenced by the strong name, as the assembly has been digitally signed when it was compiled.

This raises the question 'what happens if you don't do this step?' If you don't, you can deploy the workflow plugin, and use it in a workflow rule, but you get an error when you try to publish the workflow rule. Unfortunately the message you get is unhelpful; it's a variation of 'An unexpected error has occurred'. If you dig deeper, and enable tracing on the server, you do get a useful message in the w3wp log, such as the following:

Workflow compilation failed:WF363: Type MyActivities.Demo, MyAssembly, Version=4.0.0.0, Culture=neutral, PublicKeyToken="7766554433221100 is not marked as authorized in the application configuration file.

Once you find it, this message is pretty useful, as it tells you most of the problem, and also references the assembly in exactly the same way as you need to in web.config

Wednesday 15 October 2008

Report Wizard: Query execution failed for data set 'DSMain'

There is a problem with the CRM 4.0 Report Wizard that can result in an error like the following:
An error has occurred during report processing.Query execution failed for data set 'DSMain'.The column 'accountid' was specified multiple times for 'account0'. The column 'accountid' was specified multiple times for 'opportunity1'.

Explanation of the problem
The ultimate cause is how the Report Wizard stores the Filtering Criteria for reports based on the account entity. The Report Wizard stores the query for any criteria as a combination of all fields in the account entity, and all fields in the related primary contact. When the report is run, the SQL query attempts to use the results of the following (or similar) as a table alias:

select DISTINCT account0.*, accountprimarycontactidcontactcontactid.* from FilteredAccount as account0 left outer join FilteredContact as accountprimarycontactidcontactcontactid on (account0.primarycontactid = accountprimarycontactidcontactcontactid.contactid) where (account0.statecode = 0)

This returns two fields called accountid (one from the account entity, and one from the contact), which breaks the main SQL query for the report, and gives the error above.

Resolution
The way to resolve this is to ensure that, when you create the report with the Report Wizard, you do not specify any criteria for the account entity. This will cause the Report Wizard to store the query as solely against the account entity. Once you’ve created the report, you can happily edit the default filter to whatever you want, and the report will work fine – the key factor is not having any criteria when you first create the report.

Unfortunately there’s not an easy way to fix existing reports with this problem – it should be possible to edit the data in the DefaultFilter column in the reportbase table, but this is unsupported. I’d suggest in this scenario that you’re best off recreating the report from scratch

Saturday 11 October 2008

SQL Server: The instance name must be the same as computer name

This is something I’ve posted about on newsgroups, but one of my colleagues encountered it recently, and I think it deserves a blog entry.


The CRM Environment Diagnostics Wizard may throw the error ‘The instance name must be the same as computer name’. The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed, and the error from the CRM Environment Diagnostics Wizard indicates that the entry in sysservers does not match the current computer name.


You can diagnose and resolve this by using some SQL system stored procedures. One of them lists the data in sysservers, the other 2 allow you to modify the data to reflect the current machine name.


To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0


If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.


To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ -- where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name



If you use named instances, refer to them in the form SERVERNAME\INSTANCENAME. It may then be necessary to restart SQL Server after these changes, but I'm not sure of this. It can't harm though if you can.



There is a KB article about this here. This descibes a similar solution, but be warned of a couple of minor issues with the solution - it fails to specify that quotes are required around the parameters to sp_dropserver and sp_addserver, and I have a feeling (though can't provide concrete evidence) that running sp_helpserver is more reliable than select @@servername.

Friday 10 October 2008

Excel Data Type issues with OLEDB and SSIS

I recently met an annoying issue when reading Excel data in SSIS. The issue was when a column contained a mix of data-types – in this case string and numeric information. The problem comes from the fact that Excel does not have a concept of column data-types, and the issues in SSIS come from the way the OLE-DB provider for Excel attempts to resolve this.

I found the information I needed in an excellent blog post here, and I won’t repeat the content here. One additional point that is specific to SSIS is how to add the IMEX extended property to the connection string. In Visual Studio 2005 (I’ve not checked other versions) you can only do this by editing the ConnectionString property directly in the Properties window – the dialog box doesn’t offer this option. See the image below.

Sunday 14 September 2008

SQL Linked Server by IP Address

A recent SQL Server problem I had was to setup a Linked Server from one SQL 2005 server to another, with a couple of specific requirements: The destination server could only be accessed by IP address, but the customer wanted to be able to identify the server by name in SQL code.

This should have been simple, but as I'm blogging about it you may be able to guess that it wasn't quite so straightforward...

It started promisingly. This is how to add a SQL linked server via SQL Management Studio if you can access it by name:


If you want to specify the other parameters, it seemed to make sense to specify 'Other data source', and 'Microsoft OLE DB Provider for SQL Server': That was easy... till I tried to query the server, which failed with a timeout. Then, when I opended the properties of the Linked Server, the Data Source information had gone:


If you specify the OLE DB Provider for SQL Server, then the Data Source is lost.

To get round this, I had to add the linked server via the sp_addlinkedserver system stored procedure, and specify a provider of 'SQLNCLI' (the native SQL client), and not SQLOLEDB (the OleDb provider for SQL Server). The following example shows how to do it.

sp_addlinkedserver
@server = 'REMOTESQL' -- Name used in queries
, @provider = 'SQLNCLI' -- SQL Native Client
, @srvproduct = '' -- Cannot be null, and if 'SQL Server' then you cannot specify the datasrc
, @datasrc = 'SERVER=10.0.0.1' -- IP Address of the server
, @catalog = 'MYDB' -- database name on the server, if required

Tuesday 9 September 2008

Notification.asmx

While investigating a stability issue on a customer's CRM 3.0 Server recently, I figured I needed to identify what the calls to notification.asmx were. These calls are made every 30 seconds, and can be identified in the IIS logs as follows:


2008-09-08 11:11:44 192.168.2.107 POST /MSCRMServices/notification.asmx


Each call results in a SQL query to the MSCRM database, like the following:


exec sp_executesql N'Select EventId, EventData, CreatedOn From Notification Where CreatedOn > @CreatedOn', mailto:N datetime', @CreatedOn = 'Aug 14 2008 3:08:02:370PM'


A reasonably detailed check on the internet suggested nobody else knew what notification.asmx was doing, or if they did, they weren't telling.


I won't bore you with the details of how I found it, other than my past experience with CRM 1.2 helped, but in case you're interested, it's used for processing CRM 1.2-style callouts. Unfortunately this had noting to do with the ultimate problem, but at least I now know to eliminate it from my enquiries.

Monday 8 September 2008

Cannot set Price List when Previewing Form

I came across a quirk of the Preview functionality of the CRM 4.0 Form Editor. When previewing an entity with a relationship to the price list - e.g. opportunity, quote - the Price List lookup doesn't display any price lists, so you can't set one. As far as I can tell this is due to the multi-currency functionality - the lookup normally filters by currency, but this logic is missing from the preview. Manually setting a currency in the preview form doesn't help.

This caused me an issue once when testing client script, but there's a fairly simple way around it - you can still set the price list code. This is something I commonly do if the customer has just the one price list. Here is some sample code I use in the form load event:

if (crmForm.FormType == 1)
{
var o = new Object();
o.id = '{A0E0F731-E96C-DD11-AABD-0003FF74F5B7}'; /* Change this Guid */
o.typename = 'pricelevel';
o.name = 'Standard Price List';
var a = new Array();
a[0] = o;
crmForm.all.pricelevelid.DataValue = a;
}

Note that this is only appropriate in a single-currency scenario. If you have multiple currencies, you'd be better off putting the code in the change event for the currency field, and picking an appropriate price list for the currency.

Thursday 4 September 2008

SQL Timeouts in CRM - Generic SQL Error

I often find myself answering forum posts about SQL errors (the most common error is 'Generic SQL Error'). By far the most likely cause of this error is a timeout when accessing SQL server. If this is a case your options are to increase the timeout, or to try and ensure the query does not take so long.

Increase the Timeout
The SQL timeout is controlled by a registry value on each CRM server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\OLEDBTimeout

This value does not exist by default, and if it does not exist then the default value will be 30 seconds. To change it, add the registry value (of type DWORD), and put in the value you want (measured in seconds). Then you have to recycle the CrmAppPool application pool for the change to take effect (this is a step most Microsoft documentation omits to mention); do this we IISReset, or less drastically via iisapp.vbs

Reduce the time taken by the query
This may not be so simple, as you may have little control over the query. If the query is run as a result of your code (e.g. through a RetrieveMultiple request), then you may be able to make useful changes. For example, RetrieveMultiple requests on activities are not necessarily processed very efficiently by CRM (the problem is the way that is accesses the activity parties), and I've been able to make significant improvements by using a FetchXml query instead, which gives closer control over the joins used.

Otherwise, the other query optimisation option is to add indexes in SQL Server. This is a massive topic in its own right (I used to deliver 5 day training courses just on this topic), so I'm not going to go into detail here. The general steps are:
  1. Identify the SQL query that takes the time - I use CRM tracing for this - http://support.microsoft.com/kb/907490
  2. Use the SQL Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes

There are 2 important things to take into account:

  1. Although adding an index may improve the performance of one query, it can adversely affect other SQL operations - most obviously data updates. There is no easy solution to this, though the SQL Profiler can help you if you capture and analyse a representative sample of SQL operations
  2. Some out-dated CRM documentation suggested that it is unsupported to add indexes to the MSCRM database. However, adding indexes is supported, providing the index does not implement any constraints (i.e. it's not a UNIQUE index)

Monday 1 September 2008

CRM SDK Update - 4.0.6

There's been a new release of the CRM 4.0 SDK - available here http://www.microsoft.com/downloads/details.aspx?FamilyId=82E632A7-FAF9-41E0-8EC1-A2662AAE9DFB&displaylang=en

There are no major changes in this release, but there are a few aspects of note:
  1. The Plug-in example code now uses the DynamicEntity class as recommended
  2. The code for use of ImportXml, ExportXml and PublishXml looks like it now passes all required XML nodes
  3. There are now instructions for setting up a web reference in Visual Studio 2008
  4. The PrependOrgName client-side function is now documented (and hence supported)

Sunday 31 August 2008

Hiding System Views in CRM 4.0

In CRM 3.0 it was relatively easy to hide one of the built-in system views - all you had to do was share the view with an empty team, which converted the view to a userquery entity. However, CRM 4.0 does not support this.


There is an alternative route, but it required plug-in coding. Rather than have to write code for each deployment, I created a standard plug-in that can be used to hide any views, based on some XML configuration. The source code and compiled code is available on the MSDN Code Gallery, along with a sample configuration file.

Friday 29 August 2008

Plug-ins - differences between Target and Image Entity

In a plug-in there are potentially several ways to access entity data relevant to the plug-in action. For example, on the create message you can access the data on the new entity instance in one of the following ways:
  1. Via the Target InputParameter
  2. Via an Image Entity registered on the step
  3. Via a Retrieve request in the plug-in code

These do not always work in the same way, as follows:

Availability of the data by stage

The general rules are:

  1. InputParameter is available in all stages. It can be modified in the pre-stage, but changing it in the post-stage will have no effect
  2. A PostImage Entity is available in the post-stage, and a PreImage Entity in the pre-stage only
  3. If using a Retrieve in the plug-in, then the data returned depends on the stage. In the pre-stage, you will see the data before the modification, whereas in the post-stage you see the data after the modification
  4. Some Image Entities are not relevant for some messages - e.g. there is no PreImage for a Create message, and no PostImage for a Delete message

Data in the Name attribute

If the message is updating CRM (e.g. a Create or Update message) then the InputParameter only contains the minimum information that needs to be saved to CRM. A consequence of this is that the name attribute of any of the following data types is null:

  • Lookup
  • Owner
  • Customer
  • Picklist
  • Boolean

So, if your code needs to access the name, then you cannot rely on the InputParameter, and have to use either the Image Entity or a Retrieve to get the data.

My preference is to use an Image Entity, mostly as this reduces the code I have to write. The CRM SDK also suggests that this is more efficient, though I've not done any thorough performance testing on this to determine if this is relevant.

Thursday 31 July 2008

"Invalid Domain Name" error when registering plug-ins

On a couple of occasions recently I've had an error "Invalid domain name. Domain name is either invalid or unreachable" when registering a plug-in with the RegisterSolutionRequest.

It looks to me like this is a spurious message, possibly caused by a timeout. My reasons for thinking this are:
- I get the message on a VPC image which is the CRM server, SQL server and domain controller. If the domain was really unreachable, that would mean AD or DNS were not working and I'd expect other problems.
- The message does not always occur. Registering exactly the same solution and steps normally works fine. It's not a persistent error.
- It only occurs for me when registering a large number of steps, which again is consistent with it being a timeout issue.

So, my general recommendation if getting this error is to try again, possibly registering fewer steps at a time. If I find out any moe on this, I'll post it here.

Update - 4 Aug 08
It seems an alternative reason for this error is if the RegisterSolutionRequest (or similar request) is submitted by code that is running as one of the built-in accounts - e.g. NetworkService. I could image how this occurs, as these accounts are identified as e.g. NT AUTHORITY\NetworkService, and the platform code may be looking for a domain called 'NT AUTHORITY'.

Only members of the Deployment Administrators role can register plug-ins, and it appears that CRM only allows user accounts to be added to this role (the dialog for adding users does give the option to add built-in security principals). Therefore it looks like it's not possible to register plug-ins via one of the built-in accounts - an example of this would be via code that itself is running in a plug-in

Friday 27 June 2008

Associated views on Quote Product and Order Product entities

The facility for adding Write-In products to the Order and Quote entities is implemented within CRM through 2 associated views on each of the Quote Product (quotedetail) and Order Product (salesorderdetail) entities. These are used for the Existing Products and Write-In Products navigation items respectively.

This can have effects elsewhere, however. If you create a custom one to many relationship from another entity to the Quote Product or Order Product entity, then only one of the associated views is used within the parent entity. This can cause a problem, because neither associated view displays all the data (one displays the write-in products only, and the other displays the existing products only). For example, the default associated view is the Existing Products view, so write-in products would not be displayed.

There are 2 possible solutions to this. One is to change which is the default view, the other is to change the filtering within one of the views. Both of these are configured via attributes of the savedquery entity. These can be modified in a supported way by using the CrmService.Update method for the savedquery entity, or in an unsupported way by modifying field values directly in the savedquerybase SQL table.

Which associated view is displayed is controlled by the IsDefault attribute - if you change this then it's your responsibility to ensure only one view has this set to true.

The filtering is applied based on the ColumnSetXml attribute, which is essentially a FetchXml expression. For example, the ColumnSetXml for the Existing Product view is:

<columnset version="3.0">
<filter type="and">
<condition column="isproductoverridden" value="0" operator="eq" />
</filter>
<column>productid</column><column>priceperunit</column><column>quantity</column><column>extendedamount</column><column>salesorderdetailid</column><ascend>productid</ascend>
</columnset>

If you wanted this view to include all Quote Products, then you could remove the whole filter and condition elements. Note that such a change would necessarily affect the Existing Product view within the Quote entity.

Friday 20 June 2008

Plugin Parameters

Although the CRM 4.0 SDK is generally pretty comprehensive, I find it doesn't contain as much information as I'd like about the information passed to plugins for each of the messages.

The following table lists the main parameters passed to plugins on the most common messages. If the message you want isn't listed here, post a comment and I'll update the table.

MessageParameterDirectionTypeComments
AssignAssigneeInputSecurityPrincipal
AssignTargetInputMoniker
CancelSalesOrderOrderCloseInputDynamicEntity
Close*ActivityCloseInputDynamicEntity
CloseStatusInputInt32
CreateidOutputGuidOnly available on the Post Stage
CreateTargetInputDynamicEntity
DeleteTargetInputMoniker
ExecuteFetchXmlInputString
ExecuteFetchXmlResultOutputString
GrantAccessPrincipalAccessInputPrincipalAccess
GrantAccessTargetInputMoniker
HandleSourceQueueIdInputGuid
HandleTargetInputDynamicEntity
Lose*ActivityCloseInputDynamicEntity
LoseStatusInputInt32
RetrieveBusinessEntityOutputDynamicEntity
RetrieveColumnSetInputColumnSetBase
RetrieveTargetInputMoniker
RetrieveExchangeRateExchangeRateOutputDecimal
RetrieveExchangeRateTransactionCurrencyIdInputGuid
RetrieveMultipleBusinessEntityCollectionOutputBusinessEntityCollection
RetrieveMultipleQueryInputQueryExpression
RetrieveMultipleReturnDynamicEntitiesInputBoolean
RetrievePrincipalAccessAccessRightsOutputAccessRights
RetrievePrincipalAccessPrincipalInputSecurityPrincipal
RetrievePrincipalAccessTargetInputMoniker
RevokeAccessRevokeeInputPrincipalAccess
RevokeAccessTargetInputMoniker
RouteEndpointIdInputGuid
RouteRouteTypeInputRouteType
RouteSourceQueueIdInputGuid
RouteTargetInputMoniker
SendEmailIdInputGuid
SendIssueSendInputBoolean
SendSubjectOutputStringThis is the subject after the tracking token has been added
SendTrackingTokenInputString
SetStateDynamicEntityEntityMonikerInputMoniker
SetStateDynamicEntityStateInputString
SetStateDynamicEntityStatusInputInt32
UpdateTargetInputDynamicEntityTo get the Primary Key, find the KeyProperty within the DynamicEntity
Win*ActivityCloseInputDynamicEntity
WinQuoteCloseInputDynamicEntity
WinStatusInputInt32


Notes:
*ActivityClose. For the Win, Lose and Close messages, one of the parameters is an activity type whose name depends on the primary entity - e.g. the Win message could have a QuoteClose or OpportunityClose entity passed to it

To gather this information I used the plugin tools described on the MSCRM Team blog. The source code for these tools can be found here:
Bulk Registration Tool
Plugin Logger

Other Links:
Plugin Development
Plugin Messages

Thursday 19 June 2008

Reports in IFrames - Updated for CRM 4.0

One of my earlier posts here and on the CRM Team Blog was about writing reports to display in IFrames and via ISV.Config. This was written for CRM 3.0, and although the principle remains the same for CRM 4.0, a couple of changes are required to the report parameters.

Extra Parameters passed by CRM
To support multi-tenancy and multi-language deployments, CRM 4.0 passes additional parameters on the query string when the 'pass record object-type...' option is set for the IFrame. These extra parameters are orgname, UserLCID and OrgLCID.

For the report to work in reporting services you will have to add parameters with these names to your report. The orgname parameter has to be a string, the UserLCID and OrgLCID can be either strings or integers. As with the type and typename parameters, these new parameters don't have to be used in the report, they just need to exist for reporting services to accept values for them on the query string.

Changes to the query string for ISV.Config buttons
In CRM 4.0, Microsoft have standardised the parameters such that they are the same for IFrames and ISV.Config buttons. This means that your report should have parameters id, type, typename, orgname, UserLCID and OrgLCID.

Wednesday 11 June 2008

'This entity is already locked' message

I recently had to debug some code that threw the 'This entity is already locked' error message (code 80043B1D) when updating an order product (salesorderdetail) entity.

This error can occur even if the state of both the order and order product allowed editing. The error occurs if the productid property is set on the BusinessEntity that is passed to the Update method. A general principle when coding with the CRM platform is that, if a property value is set on an entity passed to the Update method, then the CRM platform will assume that the value has been changed.

In this case, if the productid is passed, CRM assumes that you are trying to change the product, which is not permitted if the ispricelocked attribute is set to true on the order. So, the solution is to always ensure that you know which attributes are actually changing, and ensure that only these properties and the primary key value are passed to the update method.

Tuesday 3 June 2008

Differences between Workflow and Plugins in CRM 4.0

A common question I get is ‘what is the difference between workflow and plugins, and when should I use one or the other ?’. This article is intended to be a comprehensive answer to these 2 questions.

First of all, I want to clarify the distinction between what I consider 3 distinct categories of workflow – the terminology is my own:
  • Simple workflow. This is one or more workflow rules created within the MSCRM interface, and which do not include custom workflow activities.
  • Workflow with custom workflow activities. I’ll explain more about custom workflow activities later in this article; for now the key point is that a custom workflow activity is .Net code that is written to perform functions not possible in simple workflow, and this code can be called from a workflow rule
  • Workflows created with the Workflow Designer in Windows Workflow Foundation. It is possible to build workflows outside of the CRM user interface, but I’m not going to include them in this article.
Simple workflows
The main characteristics of simple workflows are:
  • * Creating a simple workflow involves no code
  • Workflows run only on the CRM server – workflow processing is not available within a client that is offline
  • Workflows run asynchronously – any data changes made within a workflow rule will not show up immediately within the CRM user interface
  • Workflows can be run manually or automatically. The automatic running of workflows is based on several data modification events
  • Workflow rules are entity specific
  • The state and stage of a workflow instance can be viewed within the CRM user interface by any CRM user with appropriate permissions

Some limitations of workflows are:

  • Workflow rules cannot be created for all entities
  • Although workflow rules can be triggered by the most common data modification events, there are some events that don’t trigger workflow rules
  • * Simple workflows offering limited capability to perform calculations

Characteristics and limitations marked with an asterix (*) do not apply if using custom workflow activities; the others do still apply.

Custom workflow activities
Custom workflow activities allow you to extend the capabilities of workflow rules. Three common uses are to perform calculations on CRM data, to access CRM functionality that is not available in the out-of-the-box workflow activities, and to access external data.

Custom workflow activities are written as .Net assemblies which have to be registered on the CRM server. These assemblies can include information about parameters that can be passed into or out of the activity.

Plugins
A plugin is .Net code that is registered on the CRM server. A plugin assembly can be registered for one or more steps – these steps correspond to the combination of the message (event), entity and stage. An example of a step would be the Create message for the account entity on the pre-event stage. Registration of the assembly and steps is part of the plugin deployment process, and there is no direct user interaction with plugins

The main characteristics of plugins are:

  • They have to be written as .Net code
  • Although they typically run on the CRM server, they can be deployed and configured to run on a CRM client that is offline
  • They can run either synchronously or asynchronously. If they run synchronously, any data changes made within the plugin will show up immediately within the CRM user interface
  • Synchronous plugins can run on either the pre-event or post-event stage. The pre-event stage happens before data is written to the CRM database, and a plugin registered on a pre-event step can cancel the data writing and provide an error message to the user.
  • More entities can have plugins registered for them than can have workflow rules
  • Plugins can run on more events than can workflow rules. An example is that plugins can run on data retrieval, not just modification events

The main limitations of plugins are:

  • Plugins cannot be run manually; they only run on the steps for which they are registered
  • There is no user interaction with plugins, other than error messages that might be throw by a synchronous plugin

Sample Scenarios
So, having covered the main characteristics and limitations of simple workflows, custom workflow activities and plugins, when should you choose one or another ? Here are some common scenarios:

What you want to achieve can be done with a simple workflow, and it is acceptable or desirable for this to happen asynchronously and on the server only
In this case I’d go with simple workflows; there’s no point writing code if you don’t have to.

What you want to achieve has to run synchronously
If so, workflow won’t do, so it would have to be a plugin. An alternative could be to use client script, but I don’t intend to complicate this article by including this in any more detail

You need to be able to cancel the data operation
A pre-event plugin is the only option covered here, though again client script should be considered

You want to give users the discretion to run the operation manually
Here you’ll need a workflow. Whether or not you need a custom workflow activity depends on the complexity of the operations. Again, there may be an option outside of the scope of this article – to write an ASP .Net application that is called from an ISV Config button

You need to write custom code, but you want users to decide whether this code should run, and under what circumstances
In this case I’d go with a custom workflow activity, as you could make this available for users to add to their own workflows

Further Information
There is more detail about how to write and deploy plugins and custom workflow assemblies within the CRM 4.0 SDK, though unfortunately there’s not a lot of information about when to choose one or the other.

The classroom training material ‘Extending Microsoft Dynamics CRM 4.0’, course number 8969, should have more information both about building plugins and custom workflow assemblies, and when to use them. This course will also cover other solution technologies such as client script and ASP .Net extensions.

One of the MS team, Humberto Lezama has also produced a useful matrix indicating the relative merits of workflow and plugins. This can be found here

Monday 2 June 2008

CRM SDK version 4.0.5 has been released

The CRM 4.0 SDK has been updated again, and is available for download here

A decent overview of the changes can be found on the CRM Team Blog

Thursday 22 May 2008

Blog Update - Links to Code Gallery Resources

I've added an extra section to this blog. This is a list of all my published resources on the MSDN Code Gallery, along with a link to all other CRM-related resources there. At the time of posting, there were 66 resources in the code gallery with a CRM tag.

Posting on Microsoft Dynamics CRM Team Blog

I've been invited to post again on the Microsoft Dynamics CRM Team Blog. This time I've included information about plugin messages, and how a couple of tools can be used to record information about all plugin events on a CRM 4.0 deployment. The article is here: http://blogs.msdn.com/crm/archive/2008/05/21/plugins-which-message-and-which-pipeline.aspx

The two tools are both available on the MSDN Code Gallery:
http://code.msdn.microsoft.com/crm40pluginbulk
http://code.msdn.microsoft.com/crm40pluginlogger

Wednesday 21 May 2008

Some Plugin Tools

I've posted 2 resources (.Net projects) to the MSDN Code Gallery to assist with writing and deploying plugins for CRM 4.0.

One tool is a logging tool This writes details about any plugin event to SQL tables. This records standard event data - date, message, primary entity, stage and pipeline - and also any data passed in property bags - InputParameter, OutputParameter, PreEntityImage, PostEntityImage and SharedVariable. I find this very useful when determining what information is passed for each message.

The other tool is a registration tool. This is a set of enhancements to the plugindeveloper that is included in the CRM 4.0.4 SDK. The main enhancement is to allow registration of a plugin against all messages or all entities. I use this in conjunction with the logging tool to allow me to quickly build an environment that captures all customisable plugin events on a CRM deployment. This tool makes use of the sdkmessagefilter class to determine which combinations of plugin event and message are available on a CRM deployment

Both resources on the Code Gallery contain the full source code, which is made available under the Microsoft Public License (Ms-PL), as well as installation instructions

More information about these tools, and an explanation of the code will shortly be posted on the Microsoft Dynamics CRM Team Blog. Rather than repeat the content here, I'll post a link when it's live.

Updated 21-05-08: The Team Blog link is http://blogs.msdn.com/crm/archive/2008/05/21/plugins-which-message-and-which-pipeline.aspx

Monday 21 April 2008

CRM 4.0 Upgrade error. An item with the same key has already been added

Recently when testing a CRM 4.0 upgrade for a customer, I got the error 'An item with the same key has already been added'. I'm not the first to have met this problem (see the MS newsgroup), and probably won't be the last).

From what I've found so far there are 2 possible causes, both to do with CRM believing there are duplicate field names. Either a duplication has occured between a custom field, and a virtual field, or you have somehow got a duplicate entry in FieldXml.

Duplication between a custom field, and a virtual field
For certain field types (boolean, picklist and lookup), CRM creates an additional attribute in the metadata for the text of the field. This attrbiute is not physically stored, and is considered to be a 'virtual' field. The virtaul attribute has the name of the base field + a suffix of 'name'. For example, the prioritycode picklist field results in a corresponding prioritycodename virtual field, and a custom lookup field new_accountid would result in a virtual field new_accountidname (there is also a new_accountiddsc virtual).

This can cause a problem if you created an additional, physical field with the same name as a virtual field. CRM will let you do this, but the upgrade could fail.

To determine if this is a potential problem, run the following SQL script in the METABASE SQL database. This will return a list of any duplicates (you'll need dbo rights to run the script)

select e.name as entity, a.name as attribute, count(*)
from attribute a join entity e on a.entityid = e.entityid
group by e.name, a.name
having count(*) > 1

If you've found duplicates, your only solution is to remove the duplicate field from CRM.

Duplicate entry in FieldXml
CRM stores metadata in several places in CRM. One of them is the OrganizationUIBase table in the MSCRM database. Within this table, some data is stored in the FieldXml field. This is an XML document that looks something like this:

<entity name="prism_utilisationperiod" objecttypecode="10003">
<fields>
<field name="prism_utilisationperiodid" requiredlevel="na">
<displaynames>
<displayname description="UtilisationPeriod" languagecode="1033" />
</displaynames>
</field>
<field name="createdon" requiredlevel="na" format="date">
<displaynames>
<displayname description="Created On" languagecode="1033" />
</displaynames>
</field>
</fields>
</entity>

There should only be one field element for each CRM attribute, but the problem I had was that a field element was duplicated.

To determine any duplicate fields, use the following SQL in the MSCRM database:

declare @fieldXml nvarchar(max), @otc int, @idoc int
declare curEntity cursor fast_forward for
select ObjectTypeCode, FieldXml from OrganizationUIBase where InProduction = 1
open curEntity
fetch next from curEntity into @otc, @fieldXml
while @@fetch_status = 0
begin
EXEC sp_xml_preparedocument @idoc OUTPUT, @fieldXml
if exists ( select name as field from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64)) group by name having count(*) > 1 )
select @otc as otc, name as field, count(*) as occurences
from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64))
group by name
having count(*) > 1
EXEC sp_xml_removedocument @idoc
fetch next from curEntity into @otc, @fieldXml
end
close curEntity
deallocate curEntity

This will list the objecttypecode and attribute name of any duplicates. If you have duplicates, the resolution is to export the customisations for the relevant entities, delete any duplicate field elements from the customisation xml, then reimport the xml and publish the entities.

Credit is due to Michael Hohne and Kesh Patel who've contributed to finding the problems and solutions.

Thursday 3 April 2008

Email Templates with Custom Entities

I've recently been doing some development work creating emails based on templates. In most cases this is straightforward, once you've applied CRM 3.0 Update Rollup 1, 2 or 3, and also updated any web references. However, there are a couple of quirks I found when trying to use custom entities.

First; you can only use Global templates with custom entities. CRM 3.0 does not allow you to create an entity-specific template. This led to the first quirk; I was using a QueryExpression to find a specific template by name, and also templatetypecode (as CRM allows you to create templates with the same name but on different entities). When searching for a Global template, the templatetypecode is systemuser, rather than null as I was expecting.

Next, when using InstantiateTemplateRequest, it failed if the ObjectType were set to any custom entity. The error message is the unhelpful 'An unexpected error occurred'. Again, the solution is to treat a Global template as if it were actually a template for the systemuser entity. Therefore to make it work you have to specify an ObjectId and ObjectType for a systemuser - it doesn't seem to matter which systemuserid you use.

Putting this together, the following code shows how to create an email based on a template for a custom entity:


public email CreateEmailFromGlobalTemplate(string TemplateName, string EntityTypeName, Guid ObjectId, Guid SomeUserId)
{
QueryByAttribute qa = new QueryByAttribute();
qa.EntityName = EntityName.template.ToString();
qa.Attributes = new string[] {"title", "templatetypecode"};
qa.Values = new object[] {TemplateName, EntityName.systemuser.ToString()}; // Use systemuser for global template
ColumnSet cs = new ColumnSet();
cs.Attributes = new string[]{"templateid"};
qa.ColumnSet = cs;
BusinessEntityCollection bec = svc.RetrieveMultiple(qa);
if (bec == null && bec.BusinessEntities.Length == 0)
throw new Exception("Cannot find template");
Guid templateId = ((template) bec.BusinessEntities[0]).templateid.Value;
InstantiateTemplateRequest req = new InstantiateTemplateRequest();
req.ObjectId = SomeUserId; // Any valid systemuserid
req.ObjectType = EntityName.systemuser.ToString(); // Use systemuser for global template
req.TemplateId = templateId;
InstantiateTemplateResponse resp = (InstantiateTemplateResponse) svc.Execute(req);
if (resp == null && resp.BusinessEntityCollection.BusinessEntities.Length == 0)
throw new Exception("Cannot create email");
email newMail = (email) resp.BusinessEntityCollection.BusinessEntities[0];
newMail.regardingobjectid = new Lookup();
newMail.regardingobjectid.type = EntityTypeName;
newMail.regardingobjectid.Value = ObjectId;
return newMail;
}


One more thing to bear in mind: If you use InstantiateTemplateRequest then the code has to run under a CRM context of a live user, rather than the SYSTEM user, otherwise you will get the error 'The specified users settings have not yet been created' - I guess because CRM also looks at user templates

Monday 3 March 2008

Workflow assembly. Trying to make a Lookup parameter optional

When creating a workflow assembly for CRM 3.0, CRM has no proper understanding of optional parameters. The best way to cope with this is to specify a default value in the workflow.config file, but I found a weird issue with parameters of type lookup.

What I expected to able to do was specify a default of {00000000-0000-0000-0000-000000000000} which would map to Guid.Empty. Creating a rule in Workflow Manager was fine, but when it came the running the rule, it failed prior to calling the assembly with the following error in Workflow Monitor:

'Error code = 800040005. The Microsoft CRM server is unavailable. There is a problem communicating with the Microsoft CRM server.' This is obviously an incorrect error message, given the message itself was written to the CRM server. Further investigation showed the problem was with the above Guid, and anything other than an empty Guid works fine, so I now use {FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}

Wednesday 30 January 2008

MSDN Code Gallery

Microsoft have just made the new MSDN Code Gallery live. This is a site for sharing sample code, and I've posted some of my example code from previous blog articles. So far I've posted the following 4 resources:

MSCRM: Automate Local Data Group creation

Microsoft CRM report of all files related to an account

MSCRM - web page hosting to extend client script

Writing reports to use in IFrames and from ISV.Config in Microsoft Dynamics CRM

For reference, if you want to search for CRM examples, there's an emerging tagging protocol to tag all CRM examples as both CRM and Dynamics, so you can search on either tag. I'm also tagging samples with reports as 'Reporting Services'

Saturday 26 January 2008

Scheduling and emailing reports with CRM

A common customer request is to be able to schedule CRM reports and automatically send the report to one or more users by email. This can be done without the need for any code, but needs a combination of configuration within both CRM and Reporting Services.

CRM Configuration
CRM reports are designed to be run under the context of the user running the report so as to return only the data that user has permission to see. Due to this configuration, Reporting Services will not permit you to schedule CRM Reports out of the box (if you try, you'll get the error 'Subscriptions cannot be created because the credentials used to run the report are not stored') . The easiest way to resolve this issue is to download the CRM Report Scheduling Wizard from here and install it on the CRM server.

Then, navigate to Workplace, Reports in CRM, select the report you want to schedule, and go to More Actions, Schedule Report. As you will do the scheduling later within Report Manager, I'd suggest you select the following options:
  • Generate snapshots 'On demand'
  • 'Make snapshots available only to me'
  • Specify any parameter values
  • You then need to specify the credentials under which the report will run. This needs to be a valid CRM user
  • 'Yes, generate the snapshot now'

Reporting Services configuration
The rest of the configuration is done via Reporting Services. First of all, you may have to configure the email properties of Reporting Services. To do this open RSReportServer.config, which by default will be in the Reporting Services\ReportServer directory under the SQL installation directory in Program Files. Within the 'RSEmailDPConfiguration' element you will need to set values in, as a minimum, the 'SMTPServer' and 'From' elements. You may need to apply other settings. For more information see the documentation for SQL 2005 or SQL 2000

Scheduling the Report in Report Manager
You can now schedule the report. Browse to Report Manager (by default it will be in the Reports virtual directory), go to the OrganisationName_MSCRM folder and the report you have scheduled (it's name will have the suffix On demand Snapshot). On the toolbar you should have a button 'New Subscription'. Click this, select 'Report Server E-mail' in the Delivered by drop down, and set all other options as required. (If 'Report Server E-mail' is not an option, go back to the instructions in the above paragraph

Monday 14 January 2008

CRM 4.0 Error with ISV pages: MultipleOrganizationSoapHeaderAuthenticationProvider

If you have a CRM 3.0 Server with custom ASP.Net pages in a virtual directory within the CRM website, when you upgrade the server to CRM 4.0, the custom pages fail, giving the error:

Microsoft.Crm.WebServices.Crm2007.MultipleOrganizationSoapHeaderAuthenticationProvider, Microsoft.Crm.WebServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' doesn't exist

You can also get the same error when creating a new virtual directory within the CRM website.

The reason for the error is that CRM adds 2 HttpModules (MapOrg and CrmAuthentication) to web.config in the root of the CRM website. The CrmAuthentication HttpModule is in the microsoft.crm.webservices.dll assembly, which is not in the path of the custom virtual directory.
There are two solutions to this. Either:
  1. Ensure microsoft.crm.webservices.dll can be found. To do this, add it to the global assembly cache, then run iisreset (or recycle the CrmAppPool application pool)
  2. Remove the CrmAuthentication HttpModule from the virtual directory. I haven't fully investigated whether this adversely impacts use of the CRM 4.0 web services endpoint, but can be safely done if the ASP >net pages use the CRM 3.0 web services endpoint (or don't access CRM web services at all). To remove the HttpModule, add the following to the web.config in the custom virtual directory (within the system.web element):

<httpModules>
<remove name ="CrmAuthentication" />
</httpModules>