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

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.

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.

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)

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)

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.

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.

"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

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.

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.

Message	Parameter	Direction	Type	Comments
Assign	Assignee	Input	SecurityPrincipal
Assign	Target	Input	Moniker
CancelSalesOrder	OrderClose	Input	DynamicEntity
Close	*ActivityClose	Input	DynamicEntity
Close	Status	Input	Int32
Create	id	Output	Guid	Only available on the Post Stage
Create	Target	Input	DynamicEntity
Delete	Target	Input	Moniker
Execute	FetchXml	Input	String
Execute	FetchXmlResult	Output	String
GrantAccess	PrincipalAccess	Input	PrincipalAccess
GrantAccess	Target	Input	Moniker
Handle	SourceQueueId	Input	Guid
Handle	Target	Input	DynamicEntity
Lose	*ActivityClose	Input	DynamicEntity
Lose	Status	Input	Int32
Retrieve	BusinessEntity	Output	DynamicEntity
Retrieve	ColumnSet	Input	ColumnSetBase
Retrieve	Target	Input	Moniker
RetrieveExchangeRate	ExchangeRate	Output	Decimal
RetrieveExchangeRate	TransactionCurrencyId	Input	Guid
RetrieveMultiple	BusinessEntityCollection	Output	BusinessEntityCollection
RetrieveMultiple	Query	Input	QueryExpression
RetrieveMultiple	ReturnDynamicEntities	Input	Boolean
RetrievePrincipalAccess	AccessRights	Output	AccessRights
RetrievePrincipalAccess	Principal	Input	SecurityPrincipal
RetrievePrincipalAccess	Target	Input	Moniker
RevokeAccess	Revokee	Input	PrincipalAccess
RevokeAccess	Target	Input	Moniker
Route	EndpointId	Input	Guid
Route	RouteType	Input	RouteType
Route	SourceQueueId	Input	Guid
Route	Target	Input	Moniker
Send	EmailId	Input	Guid
Send	IssueSend	Input	Boolean
Send	Subject	Output	String	This is the subject after the tracking token has been added
Send	TrackingToken	Input	String
SetStateDynamicEntity	EntityMoniker	Input	Moniker
SetStateDynamicEntity	State	Input	String
SetStateDynamicEntity	Status	Input	Int32
Update	Target	Input	DynamicEntity	To get the Primary Key, find the KeyProperty within the DynamicEntity
Win	*ActivityClose	Input	DynamicEntity
Win	QuoteClose	Input	DynamicEntity
Win	Status	Input	Int32

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