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.

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

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)