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

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.

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.

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.