David Jennaway - Microsoft Dynamics CRM

Using WSDL Proxies with CRM Online. It's outside of North America

2012-01-31T13:30:00.005Z

I've been spending more time than I'd like using WSDL Proxies with CRM Online (i.e. when I can't use the .Net 4.0 OrganizationServiceProxy class). I'll write up some more about this soon, but this is a quick post about a specific issue with connecting to CRM Online for an organisation in EMEA, rather than North America, which I've not found documented anywhere.

I was basing my code on the wsdlbasedproxies example in the CRM 2011 SDK. Once you find it, this code is reasonably well documented.

However, when testing it, I could connect to the IDiscoveryService without problems, but continually got the error 'An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail' when connecting to the IOrganizationService.

Ultimately the issue was with the AppliesTo constant. The setup.txt instructions tell you to set this based on data in the element from the Discovery.svc wsdl. This gave me "urn:crmemea:dynamics.com" when connecting to an Online organisation in EMEA, which worked for the IDiscoveryService, but not IOrganizationService.

After more time than I'd like, I found out that the IOrganizationService in EMEA works when AppliesTo = "urn:crm4:dynamics.com". My guess is that "urn:crm5:dynamics.com" would be correct in Asia (if anybody has a sample login to try, let me know and I'll confirm it one way or another).

So, this needed a few minor code changes to the code in Online\program.cs, as you need different tokens for each service. For EMEA, I used the following:

private const string AppliesToDiscovery = "urn:crmemea:dynamics.com";
private const string AppliesTo = "urn:crm4:dynamics.com";

static void Main(string[] args)
{
//Authenticate the user
SecurityToken tokenDiscovery = Authenticate(UserName, UserPassword, AppliesToDiscovery, Policy, IssuerUri);
SecurityToken token = Authenticate(UserName, UserPassword, AppliesTo, Policy, IssuerUri);
//Execute the sample
string serviceUrl = DiscoverOrganizationUrl(tokenDiscovery, OrganizationUniqueName, DiscoveryServiceUrl);
ExecuteWhoAmI(token, serviceUrl);
}

Selecting the correct workflowid

2011-11-04T18:09:00.001Z

A very quick one, which is mostly an aide-memoire for myself, but others may find useful.

I occasionally need to run CRM workflows programmatically using an ExecuteWorkflow request. This takes a workflowid as a parameter, but the question is, which one ? If you look at the workflow entity, you'll find several records for a workflow with a given name. The logic I use to determine which is the correct on is:

statecode = 1 (Activated)

activeworkflowid is not null

But use the workflowid value of the record for which activeworkflowid is not null. This seems a but counter-intuitive to me, hence this post

PartyList attributes and the plugin event pipeline

2011-07-13T09:55:00.002+01:00

This should be a quick post about a subtlety with the plugin event pipeline. I recently wrote a plugin that could modify the data that's submitted when updating an activity record. This should have been a straightforward plugin on the Pre event that modified the Target InputParameter, and it all worked fine, except for partylist fields (such as the resources field on the serviceappointment entity, or optionalattendees on the appointment entity). Essentially, any changes I made to these fields in the plugin were ignored.

Fortunately, there is a solution, and it depends on the stage that you register the plugin on. If you register on stage=20 (i.e. within the transaction), your changes are ignored. However, change the registration to stage=10 (before the transaction), then it does work. There's no documentation on this, but I expect it is due to how the partylist data is saved. This data is written to the activityparty table in the database, and I expect that the SQL for this is already fixed at the start of the transaction, and hence is unaffected by changes in the plugin code.

Unexpected error with ConditionOperator.In and typed arrays

2011-05-25T18:00:00.004+01:00

I just met a bizarre error when using the Crm xrm assembly when using the ConditionOperator.In in a query. In this case the query was to find all notes related to a list of CRM entities, and the error was "Condition for attribute 'annotation.objectid': expected argument(s) of type 'System.Guid' but received 'System.Guid[]'". I was using almost identical code to some code that did work, but there was a subtle difference in the overloads of some of the xrm methods.

Consider the following code, which works:

QueryExpression q = new QueryExpression("annotation");
Guid g1 = Guid.NewGuid();
Guid g2 = Guid.NewGuid();
q.Criteria.AddCondition(new ConditionExpression("objectid", ConditionOperator.In, new Guid[] { g1, g2 }));

However, change the last line to the following, and it fails with the error above:

q.Criteria.AddCondition("objectid", ConditionOperator.In, new Guid[] { g1, g2 });

On the face of it, you'd expect identical behaviour, but it looks like the problem is due to the parameter overloads on the different methods. The constructor for ConditionExpression takes 5 overloads, and the compiler will use System.Collections.ICollection for the array of Guids. However, the AddCondition method only offers one type for the third parameter (params object[]). The result of this is that the code fails because the parameter is interpreted as object[] {new Guid[] { g1, g2 }}.

Interestingly, other code can also work, e.g.

q.Criteria.AddCondition("objectid", ConditionOperator.In, new object[] { g1, g2 });
q.Criteria.AddCondition("objectid", ConditionOperator.In, g1, g2);

Options for upgrading ASP .Net extensions for CRM 2011

2011-04-09T10:30:00.001+01:00

I like April. One reason is that it's the start of my MVP renewal cycle. After April 1st April I will either have been renewed, or not, and I feel less circumspect about making critical comments about decisions Microsoft have made. So, ASP .Net extensions with CRM 2011. Since the first release of CRM (CRM 1.0 or CRM 1.2 depending on your country), a major extension point for On-Premise CRM implementations was to develop ASP .Net extensions and deploy them within the CRM web site. Having a supported way to place these extensions within the CRM web site was important for several reasons:

To allow relative Urls in IFrames, ISV.Config and SiteMap. Main reasons for that are to cope with IFD environments where different domain names are provided for internal and external access, and to avoid configuration issues exporting/importing between environments
To allow single-sign on and impersonation, so the code could act on behalf of the CRM user, without needing to re-enter their credentials
To maintain the Site Origin (aka same site of origin). This is an important consideration as Internet Explorer security will only permit code interaction between pages if it considers that they are part of the same site. For example, the ability to pass data to a dialog using window.dialogArguments, or the ability to access objects on a calling window via window.opener both depend on the pages being in the same site

However, CRM 2011 puts significant restrictions on putting your ASP .Net extensions within the CRM web site. Essentially, the only supported option is to retain existing extensions that use the CRM 4 endpoint. So, what happens to the points above if you can't put your ASP .Net extensions in the CRM web site:

You'll have to use absolute Urls. This makes deployment between environments (e.g. from development to live) harder, as the Urls would have to changed. In small-scale environments this would be just a manual task, but in larger environments you may decide to build a process to automate this. Overall, I see this as a major annoyance, but not a major problem
Microsoft have put work into making single sign-on work across web sites. This depends on setting up Secure Token Services, which incurs some administrative and deployment overhead. There's an additional deployment overhead of setting up a new web site for the extensions, and configuring access to it. I've not tested this fully, but assuming it works as promised, this should resolve the single sign-on and impersonation issue
This is the big problem area. I don't think same site of origin can be maintained with the ASP .Net pages outside of the CRM web site, which effectively removes support for a common type of extension that was possible in all previous versions (Note, IE 8 has some settings that affect how Site Origin is applied, and this might help, but IE 7 is a supported browser for CRM 2011, so this cannot be a universal solution)

So, what can/should you do with your ASP .Net extensions that you wrote for CRM 4.0, or were intending to write. I see 3 main options:

Don't upgrade your code. ASP .Net code that uses the CRM 4 web service endpoint will still work in the ISV folder, and Microsoft have not said it is unsupported. This is the simplest option, but it means you need to maintain CRM 4 code, and you'll still have to address the issue when CRM 6 comes out. You could cross your fingers and hope that, in CRM 6, Microsoft reintroduce support for ASP .Net extensions within the CRM web site
Upgrade the code to use the CRM 2011 endpoint, and deploy it in a separate web-site from CRM. As stated above, this causes extra deployment overhead (which I consider is a significant overhead, which is often under-estimated), and you won't be able to use same site of origin, so you have to expect some limitations
Rewrite the code as web resources (Silverlight, or HTML with javascript). Microsoft have introduced quite a lot of integration points in CRM 2011 that makes this a powerful option. This has a major advantage that the resources are necessarily hosted within the CRM web site, and can be deployed as part of a CRM solution. However, these are client-side technologies and some extensions would need extra work to build (e.g. extensions that access a database on another server). My biggest problem though is the development effort required to rewrite code in substantially different technologies, and these are technologies that are not as mature as ASP .Net

Of these, I don't like any of the options. Numbers 2 and 3 would be necessary if you need to support CRM Online, but for On-Premise implementations there are some difficult decisions to make. Don't get me wrong, I appreciate that there are a lot of good things for developers in CRM 2011; web resources and the single sign-on across web-sites are very powerful and very welcome, and the only options for CRM Online, it's just a shame to lose some On-Premise options.

But to finish on a more positive note, another reason I like April is that this is a great month for ski touring.

Removing prompt for credentials when browsing with Internet Explorer

2011-04-08T15:50:00.005+01:00

This post isn't intended to be a complete list of solutions to issue when you are unexpectedly prompted for AD credentials when browsing with Internet Explorer, but it gives some rules of thumb regarding where to start looking. The scenario: You try accessing a page using IE, and are prompted by a 'Windows Security' dialog for AD credentials when you don't expect it.

Possible causes: There are many, but the first thing to do is work out if it's a server-side issue, or a client-side issue. A simple test is what happens if you provide valid credentials in the Windows Security dialog:

If you can then connect, then this is a client-side issue
If you are prompted again, either 2 or 3 times, then get a permission error (normally HTTP 401), then it is a server-side issue

Client-side issues If it's a client-side issue, then look at the client IE settings, and the URL of the web page: If IE considers that you have already logged into the DNS domain (the part of the url prior to the first single / - e.g. http://crm:5555/), then it should reuse these credentials and you won't be prompted to login. However, IE is picky about matching the DNS domain, so if you've already logged into http://crm, then it won't trust other aliases (e.g. http://localhost, http://crm.mydom.com, http://192.168.0.1) and will prompt for credentials.

If you're using CRM 4 and have extension pages in the ISV directory of the CRM web site, then it is best to provide the URL as a relative path (e.g. /ISV/MyCompany/MyPage.aspx) rather than an absolute path, to avoid this issue.

The IE security settings will determine whether IE will try submitting your logged-on credentials. By default, it will only do this if you connect to a site in the Local Intranet Zone, so check the IE security settings, and the zone of the web site you're connecting to

Server-side issues If it's a server-side issue, then there are many possible causes, but most of them come to Kerberos in one way or another

Possible SQL Gotcha - use of 'Not In' with NULLs and the customer attribute

2011-02-28T19:26:00.004Z

I was recently putting together a bit of SQL to illustrate the use of a NOT IN clause for a forum answer, and got some unexpected results. The query was a relatively simple example; find all accounts with no associated opportunities. So, I tried this:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT accountid FROM FilteredOpportunity)

Nice, simple query, but it returned no data (and it should have done). However, the following works fine:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT customerid FROM FilteredOpportunity)

The only difference is the use of customerid instead of accountid in the subquery. If I'd have expected the first query to work instead of the second query, as customerid is a generated field (it's generated within the Opportunity via by the SQL function COALESCE(accountid, contactid)).

This all seems weird, but it comes down to what happens with nulls. An opportunity will be associated with one of an account, or a contact. So, the subquery 'SELECT accountid FROM FilteredOpportunity' could return a null (if you have an opportunity against a contact), but 'SELECT customerid FROM FilteredOpportunity' will always return non-null values. Don't ask me why, but the presence of nulls in the subquery cause the NOT IN query to misbehave.

One way to confirm this is with another variation on the query above, which also works:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT accountid FROM FilteredOpportunity WHERE accountid is NOT NULL)

This query explicitly excludes nulls from the results on the subquery, and so it works fine.

The main lesson I took from this is to always test for nulls in the subquery when using NOT IN; another lesson is to pay close attention when using attributes that represent the composite Customer data type in CRM

For reference, the reason why I was doing this is because this is a classic example of a query that cannot be done through FetchXML, and hence cannot be written with an Advanced Find in CRM. If the primary entity is an account, contact or lead then you have a manual workaround in CRM, for example:

Create a marketing list, and populate it with all accounts
Use Advanced Find to remove from the list all accounts that have an opportunity
This will then leave you with a marketing list that contains all accounts without an opportunity

CRM 2011 RTM Release

2011-02-16T16:00:00.003Z

Seems like the day for announcements: the CRM 2011 RTM code has been released for On-Premise and Partner-Hosted environments. This is nearly 2 weeks earlier than I expected, so congratulations to the CRM product team.

The server software can be downloaded here, and that page has links to download the other components. The build number is 05.00.9688.583, which is consistent with the build number of the binaries in the release SDK.

Using CRM 4.0 assemblies on a CRM 2011 Server

2011-02-16T11:35:00.004Z

CRM 2011 Server includes a publisher policy that causes any assembly built against the CRM 4 sdk assemblies to load the CRM 5 sdk assemblies instead. There are certain circumstances where this can cause errors loading the assembly; see the end of this post for possible error messages.

One workaround is to not run the application on a Crm 2011 Server, but there is an alternative, which is to explictly tell your application not to use this publisher policy file. This is done through adding the following to the app.config file:

<configuration>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Microsoft.Crm.Sdk" publicKeyToken="31bf3856ad364e35" culture="neutral" />
<publisherPolicy apply="no" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>

This raises one more issue: in some circumstances your assembly may not be the main .exe, but a .dll loaded by another process, in which case you'll have to modify/create the .config file for that .exe. This is done by creating a file named .exe.config in the same directory as the .exe (here's an example). I have a nagging concern that I may have to do this with SSIS packages that use a custom component that use the SDK assemblies, which could get interesting, as different executables are used for in design, debug and runtime. If I do have this issue with SSIS, then I'll post a more detailed workaround (if I find it).

My hope is that this is a temporary problem that will be fixed, as the readme in the 5.0.1 version of the SDK refers to an 'incorrect Publisher Policy'. This readme also gives an explanation of this issue

One possible error
System.IO.FileLoadException: Could not load file or assembly 'Microsoft.Crm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.Crm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.Crm.Sdk, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.Crm.Sdk, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

Another possible error
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Crm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Crm.Sdk, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

CRM 2011 Documentation Released

2011-02-16T09:01:00.003Z

The CRM 2011 Implementation Guide was released last week, and it looks like the CRM 2011 SDK has also been released. As far as I can tell, the SDK is a live (as opposed to beta) release, though the hands-on labs were built on beta code. The SDK documentation has version 5.0.1, and it includes binaries with version 5.0.9688.583 which are claimed to match those for CRM 2011 Online. We'll see what version number we get with the RTM code.

And the CRM 2011 Developer Training Kit has also been released recently.

I'm deliberately using the term released to indicate that they are publicly available (as opposed to 'launch', which I consider a marketing event).

Plugin Deployment Options

2011-02-11T10:01:00.002Z

The CRM 4 SDK gives some information about the storage options when registering plugins but there are a few more considerations. I got prompted to elaborate on this in a forum post, and I think it's worth documenting this here as well:

The 3 storage options are: Database, Disk and GAC. The main differences between these are:

Database: The assembly dll is stored in the database, rather than the file system. The major advantages are that the assembly need only be deployed once if you have multiple CRM servers, and that no additional action is required to restore / redeploy the assembly either during disaster recovery, or if redeploying to an alternate server. This is the preferred option in a production environment
Disk: The assembly dll is placed in the \server\bin\assembly directory on each server. You have to ensure the dll is placed in the correct place on all CRM servers, so the deployment overhead is a little greater. I normally use this option in development environments as you can redeploy newer versions solely by file transfer, rather than reregistering. Also, if debugging, the assembly .pdb file needs to be placed in the same location; with this option it's easy to ensure the dll and pdb are from the same build
GAC: The assembly is placed in the Global Assembly Cache on each CRM server, and again you will have to do this. The GAC does allow multiple versions of an assembly, but CRM doesn't, so you don't really gain anything by using the GAC. I don't think I've ever used this option

There is one further consideration. If your plugin assembly has other dependent assemblies, then you can place this dependent assembly in the GAC whichever of the above options you take. However, if you use the Disk option, then the dependent assemblies can also be deployed into the \server\bin\assembly directory

.Net Framework versions of custom components with SQL 2008 R2 BIDS

2011-02-04T16:17:00.002Z

Rather a long title, but I couldn't think of anything shorter. Anyway, it's a topic that I would have preferred was better publicised.

SQL 2008 R2 Business Intelligence Development Studio (BIDS) will only recognise extension components (such as SSIS Data Flow Components) that are built against .Net Framework 3.5. Neither earlier nor later versions will work, and I've yet to find any useful messages to tell you why.

So far I've only done enough testing to find combinations that definitely work, as summarised in the following table.

BIDS Version	.Net Framework version of component
SQL 2005	2.0
SQL 2008	2.0
SQL 2008 R2	3.5

Stability issues with AsyncRemoveCompletedJobs

2010-12-09T11:11:00.006Z

CRM 4.0 UR 3 brought in a useful feature, the ability to configure the CRM Asynchronous Service to automatically delete records from completed asynchronous operations, and hence keep the size of the asyncoperationbase SQL table down to a reasonable size. This behaviour is configured by the registry values AsyncRemoveCompletedJobs and AsyncRemoveCompletedWorkflows

However, I recently met an issue with this behaviour, where the CRM Asynchronous Service appears to get in a state where all it is doing is deleting completed jobs, to the exclusion of all other activity. This can leave the CRM Asynchronous Service to have effectively hung (not responding to service control requests, nor polling for new jobs to process) and not to process any new jobs for a considerable period of time (in one environment, this could be several hours).

The main symptoms are:

No jobs being processed for a considerable period of time
The Crm Asynchronous Service not responding to service control requests (i.e. you cannot stop it through the Services console, so you have to kill the process)
No values reported for most performance counters (e.g. 'Total Operations Outstanding', 'Threads in use')
If you do restart the service, you see a burst of activity (including performance counters) whilst outstanding jobs are processed, then it reverts to the same behaviour as above
If you look at the SQL requests submitted by the Crm Asynchronous Service (I use the SQL dynamic management views sys.dm_exec_requests and sys.dm_exec_sessions) you see just one DELETE request and no other SQL activity

At the moment, the only workaround I have is to remove the registry values, and to use a scheduled SQL job to periodically clear out the asyncoperationbase table. Here is an example of such a script.

How to use impersonation in an ASP .Net page using IFD in CRM 4.0

2010-11-17T18:18:00.005Z

This is is common requirement, and I've never found what I consider to be a suitable explanation of what needs to be done. This post is not intended to be exhaustive, but is intended to cover the essentials in one place.

The fundamental requirement is to create a custom ASP .Net page that is accessible both internally (via AD authentication) and over the Internet (via IFD authentication), and where the code will access CRM data under the context of the user accessing the page. To do this, you need to deploy and configure your code as follows:

Deploy the ASP .Net page within the CRM Web Site (the only supported place is within the ISV directory). If you don't do this, then IFD authentication will not apply to your page
Run the ASP .Net page within the CrmAppPool, and do not create an IIS application for it. If you don't do this, then you won't be able to identify the authenticated user
Ensure that the CRM HttpModules MapOrg and CrmAuthentication are enabled. This will happen by default by inheritance of the settings from the root web.config file in the CRM web site, but I'm mentioning it here as there are some circumstances (when you don't need IFD) in which it is appropriate to disable these HttpModules. Again, if the HttpModules aren't enabled, then you won't be able to identify the authenticated user
As your code is in a virtual directory (rather than a separate IIS application), ASP .Net will look for your assemblies in the [webroot]\bin folder, so that is where you should put them (or in the GAC). The initial release documentation for CRM 4.0 stated that it was unsupported to put files in [webroot]\bin folder of the CRM web site, but this restriction has been lifted

You also need to follow certain coding patterns within your code. An example of these can be found here. Note that, Crm web services refers to both the CrmService and the MetadataService:

Ensure you can identify the organisation name. The example code shows how to parse this from the Request.Url property, though I prefer to pass this on the querystring (which the example also supports)
Use the CrmImpersonator class. All access to the Crm web services needs to be wrapped within the using (new CrmImpersonator()) block. If you don't do this you will probably get 401 errors, often when accessing the page internally via AD authentication (see later for a brief explanation)
Use the ExtractCrmAuthenticationToken static method. This is necessary to get the context of the calling user (which is stored in the CallerId property)
Use CredentialCache.DefaultCredentials to pass AD credentials to the Crm web services. If you don't do this, then you will probably get 401 errors as you'd be trying to access the web service anonymously (IIS would throw these 401 errors)

That should be all that you need on the server side. The final piece of the puzzle is to ensure that you provide the correct Url when accessing the page, which again needs a little consideration:

When accessing the page from an internal address, the Url should be of the form:
http://[server]/[orgname]/ISV/MyFolder/MyPage.aspx

When accessing the page from an external address, the Url should be of the form:
http://[orgname].[serverFQDN]/ISV/MyFolder/MyPage.aspx

This is relatively easy to achieve when opening the page from within CRM (i.e. in an IFrame, via an ISV.config button or in client script). In each case you can use the PrependOrgName global function in client script - e.g.

var u = PrependOrgName('/ISV/MyFolder/MyPage.aspx');

This function will determine correctly whether to add the organisation name to the Url. Note also that I've provided a relative Url, which will ensure the first part of the Url is always correct. As this uses a JavaScript function, you will always need to use a small piece of code to access the page, and cannot rely on statically providing the Url in the source of an IFrame, or in the Url attribute of an ISV.Config button. Any relative Urls in SiteMap should automatically get the organisation name applied correctly. Remember to also pass the organisation name on the querystring if the server code expects this (you can get the organisation name from the ORG_UNIQUE_NAME global variable)

Earlier I promised an explanation of what the server code does. This is not as complete an explanation as it could be, but the basics are:

The HttpModules identify the correct CRM organisation (MapOrg) from the Url provided, and place information about the authenticated calling user in the HttpContext (CrmAuthentication)
The ExtractCrmAuthenticationToken method reads the user context from the HttpContext, and puts the user's systemuserid in the CallerId property of the CrmAuthenticationToken
Because the CallerId is set, the call to CRM is necessarily using CRM impersonation. For this to be permitted, the execution account (see Dave Berry's blog for a definition) must be a member of the AD group PrivUserGroup. The execution account is the AD account that is returned by CredentialCache.DefaultCredentials. This is where things get a little interesting
If the request comes via the external network and IFD authentication is used, CRM handles the authentication outside of IIS and no IIS / ASP .Net impersonation occurs. Therefore CredentialCache.DefaultCredentials will return the AD identity of the process, which is the identity of the CrmAppPool, which necessarily is a member of PrivUserGroup
However, if the request comes via the internal network, AD authentication is used and IIS / ASP .Net impersonation does occur (through the setting in web.config). This impersonation will change the execution context of the thread, and CredentialCache.DefaultCredentials would then return the AD context of the caller. This is fine in a pure AD authentication scenario, but the use of the ExtractCrmAuthenticationToken method means that CRM impersonation is necessarily expected; this will only work if the execution account is a member of PrivUserGroup, and CRM users should not be members of PrivUserGroup. This is where the CrmImpersonator class comes in: its constructor reverts the thread's execution context to that of the process (i.e. it undoes the IIS / ASP .Net impersonation), so that CredentialCache.DefaultCredentials will now return the identity of the CrmAppPool, and the CRM platform will permit CRM impersonation

To finish off, here are a few other points to note:

IFD impersonation only applies when accessing the CRM platform. If you use IFD authentication, there is no way of impersonating the caller when accessing non-CRM resources (e.g. SQL databases, SharePoint, the file system); it cannot be done, so save yourself the effort and don't even try (though, for completeness, SQL impersonation is possible using EXECUTE AS, but that's it)
If you want to use impersonation, do not use the CrmDiscoveryService. The CrmDiscoveryService can only be used with IFD if you know the user's username and password, and you won't know these unless you prompt the user, which kind of defeats the point of impersonation

When is the Default Organisation not the Default Organisation

2010-11-11T17:19:00.003Z

In CRM 4.0, an organisation can be designated as the Default Organisation in the Deployment Manager tool:

However, this does not always do what some people expect. CRM has 2 distinct concepts of a 'Default Organisation', and only one of them can be set using Deployment Manager. The 'Default Organisation' that you set in Deployment is a system-wide setting whose primary use is to define which organisation database is used by code that accesses CRM 4.0 through the CRM 3.0 web services (CRM 3.0 did not support multiple organisations, so the web services had no way to specify the organisation to connect to).

The other type of 'Default Organisation' applies to users. Each user has a Default Organisation, which is the organisation that they are connected to if browsing to a Url that does not contain the organisation name. For example, if a user browses to http://crm/Excitation/loader.aspx, then the user will necessarily be taken to the Excitation organisation, but if they browse to http://crm/loader.aspx, they will be taken to their default organisation, which has no relationship to the 'Default Organisation' that is set in Deployment Manager. Each user's default organisation will be the first organisation in which their CRM user record was created.

One issue that can arise is if a user connects using a Url that does not contain the organisation name, and either their default organisation has been disabled, or their user account in their default organisation has been disabled. In this scenario, the user would receive either the error 'The specified organization is disabled' or 'The specified user is either disabled or is not a member of any business unit'. The simplest solution would be to specify an appropriate organisation name in the Url; however if this is not possible, the rest of this post describes an unsupported alternative.

Unfortunately, none of the MSCRM tools will display a user's default organisation, nor is there a supported way to change this (though there is an unsupported way - see below). All the information is stored in the MSCRM_Config database, in the SystemUser, Organization and SystemUserOrganizations tables. The following output shows some of the relevant data from these tables:

SystemUser table

Organization table

SystemUserOrganizations table

The DefaultOrganizationId field in SystemUser defines each user's default organisation, and this can be joined to the Id field in the Organization table.

The fields that define the user are a little more complex: The Id field in SystemUser is unique to each CRM user. You can join the SystemUser table to the SystemUserOrganizations table using the SystemUser.Id and the SystemUserOrganizations.UserId fields. The CrmUserId field in SystemUserOrganizations can be joined to the systemuserid field in the systemuserbase table in each CRM organisation database. Note that the same user will have a different systemuserid in each organisation database. The following query illustrates these joins, taking the user's name from one of the organisation databases (it's not stored in MSCRM_Config):

select o.UniqueName, u.FullName
from Organization o
join SystemUser su on o.Id = su.DefaultOrganizationId
join SystemUserOrganizations suo on su.Id = suo.UserId
join Excitation_MSCRM..systemuser u on suo.CrmUserId = u.systemuserid

So, that's how it fits together. As this is all SQL data, it is not difficult to modify this, but be aware that to do so is completely unsupported, and could break your CRM implementation. If you were to make any changes, make sure you backup the relevant databases (especially MSCRM_Config) before you do so.

If you did want to change a user's default organisation, please heed the warning in the preceding paragraph and backup the MSCRM_Config database. The following SQL update statement will change the default organisation of a given user, based on their systemuserid in one organisation database. The reason for writing the query this way is to ensure that a user's default organisation can only be set to an organisation that they exist in, and this query should only ever modify one record. If it modifies 0 records, then check the @systemuserid value, and if it modifies more than one record then your MSCRM_Config database is probably corrupt, and you should reinstall CRM and reimport your organisation databases (I was serious about my warnings).

declare @systemuserid uniqueidentifier
set @systemuserid = '25E1DC1D-BEC2-449B-AAD8-4A6309122AE1' -- replace this
update SystemUser
set DefaultOrganizationId = suo.OrganizationId
from SystemUserOrganizations suo
where suo.UserId = SystemUser.Id
and suo.CrmUserId = @systemuserid

One final point; CRM caches some of the data in MSCRM_Config, so you'd need to recycle the CRM application pool to sure any changes have taken effect.

The CRM 4.0 Reporting Services Connector - how it works

2010-11-09T16:27:00.001Z

The Dynamic CRM Connector for Reporting Services is a very useful component that avoids the need to configure Active Directory delegation (aka double-hop authentication) when multiple servers are used for the CRM, Reporting Services and SQL Server roles in a CRM implementation. In general, it is easy to install and use, but I'm always interested in how these things work.

How the Connector is installed and invoked
The connector is installed as a Data Processing Extension with SSRS. These extensions are registered within the rsreportserver.config file on the Reporting Server, as per the following snippet:

<Data>
<Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions" /> <Extension Name="OLEDB" Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions" />
<Extension Name="MSCRM" Type="Microsoft.Crm.Reporting.DataConnector.SrsExtConnection,Microsoft.Crm.Reporting.DataConnector" /> </Data>

All CRM reports with SSRS are configured to use a specific SSRS Data Source. When the Connector is installed, the Data Source is changed to use the MSCRM Data Processing Extension, instead of the default SQL Server extension. See images below:

Report properties showing the MSCRM Data Source

MSCRM Data Source using the SQL Server extension before the connector is installed

MSCRM Data Source using the CRM extension after the connector is installed.

There are 3 differences between these configurations:

The Connection Type, which specifies the extension
The Connection String is absent with the CRM connector. This is because the connector reads some of the database information from registry values that were created during its installation, and some from data passed to it when the report is run (see below)
The Credentials. With the SQL Server connector, standard Windows Integrated security is used - i.e. the user's AD credentials are used to connect to SQL Server. With the CRM connector, separate 'credentials' are passed to SSRS (again, see below)

What happens when a report is run
If you try to run a CRM report with the CRM connector installed, the connector will require some 'credentials', as per point no.3 above. This image shows what happens if you try to run a report from Report Manager:

Running a CRM report from Report Manager when the CRM connector is installed

These 'credentials' are not what they seem; rather they are a cunning way for the CRM platform to pass information about the current user to the CRM connector. The CRM connector expects the current user's systemuserid (a Guid) to be passed into the Log In Name box, and the organizationid (another Guid) to be passed into the Password box. These are not your login name and password.

As the report uses a data source that uses the CRM connector, the RS Report Server code calls the CRM connector code (the SrsExtConnection class in the Microsoft.Crm.Reporting.DataConnector assembly, as per the rsreportserver.config data above). The code will then:

Check that it is permitted to impersonate a CRM user. This checks that the identity the code is running under (which the identity of the ReportServer application pool, or the Reporing Services service, depending on the version of Reporting Services) belongs to the AD group PrivReportingGroup
Connect to the MSCRM_Config database to determine the correct MSCRM organization database, based on the organizationid that was passed in the 'credentials'
Connect to the relevant MSCRM organization database. Note that this is done (as was the previous step) using integrated security under the AD identity as per step 1 above
Use the SQL statement SET Context_Info to pass the calling CRM user's systemuserid into the Context_Info
Execute the SQL statement(s) within the report definition. The definition of all filtered views use the fn_FindUserGuid function to read the systemuserid from the Context_Info

What can you do with this information
One use is for troubleshooting. Checking the rsreportserver.config is a quick way to see if the connector is installed, and checking the configuration of the MSCRM Data Source will tell you if the connector is in use. Changing the MSCRM Data Source is a quick way to turn the connector on or off for test purposes.

You can also run the reports directly, rather than from CRM. Again, when troubleshooting I find it useful to run a report directly from Report Manager web interface. To do this with the connector, you need to enter the systemuserid and organizationid when prompted (see image above). These values can be read from the filteredsystemuser and filterorganization views respectively in the MSCRM database.

A further option is to run the reports via other means, such as Url Access, as described here (that article was written for CRM 3, see here for an update for CRM 4). To do this with the connector installed, you will also have to pass the systemuserid and organizationid on the query string. This is done using the following syntax:

&dsu:CRM=<systemuserid>&dsp:CRM=<organizationid>

Web.config settings - e.g. ViewState, Session

2010-10-29T09:55:00.000+01:00

A common issue raised in the CRM Development forum is of custom web pages that work correctly in a development environment, but then fail to work when deployed into the CRM web site. The most common reason for this is that the CRM web.config overrides some of the default ASP.Net configuration settings.

The relevant entries in the CRM web.config (in the root of the CRM web site) are:

<pages buffer="true" enableSessionState="false" enableViewState="false" validateRequest="false/">
<sessionState mode="Off"/>

These have 2 main consequences:

Session State is disabled. This issue is relatively easy to diagnose, as you tend to get a clear error message if trying to use session state when it is disabled
ViewState is disabled. This can be a more subtle effect, as developers often rely on viewState without necessarily being aware of it. ViewState is what allows ASP.Net web controls to maintain property values across web requests; if it is disabled then it leads to symptoms such as values not being retained, or list boxes losing their contents

The solution for viewState is straightforward. You can reenable viewState for you application either in the web.config in your application directory, or at the page level within the <@Page> directive. These looks like the following:

web.config:
<pages enableViewState="true" />

Page directive:
<@Page EnableViewState="true" />

Session state is a bit more complex, as this is configured at the web application level. Personally, I've never seen any reason to use session state within custom code in the CRM web site; CRM doesn't use this, and I find it best to mimic CRM behaviour wherever possible.

And one final point about best practise; as this post demonstrates, it is best not to rely on the default ASP .Net configuration settings, rather I find it best to always explicitly enable or disable settings in the local web.config

SDK assemblies: Versions and Processor Architecture

2010-10-28T12:45:00.000+01:00

The download of the Dynamics CRM SDK includes the sdk assemblies (microsoft.crm.sdk.dll, microsoft.crm.sdktypeproxy.dll etc). There are 4 sets of these assemblies, one in the bin directory, another in bin\64bit, and two more in the respective online subdirectories.

Given the directory naming, I'd always assumed that the assemblies in the bin directory were built for 32bit (x86) systems only, and those in bin\64bit were build for 64bit systems. I found this a little annoying, as I generally prefer to build assemblies as MSIL (AnyCPU) to avoid the need for different 32 and 64 bit deployment packages.

However, it turns out that the assemblies in the bin\64bit directory are actually built as MSIL (AnyCPU), rather than specifically as 64 bit assemblies (apparently this was an earlier deployment mix-up which it's now too late to correct). This gives me what I want, so I now always use the assemblies that, bizarrely, come in the bin\64bit directory of the SDK.

What takes up most space in an MSCRM database

2010-08-26T23:27:00.000+01:00

A frequent question is 'What is taking up most space in my MSCRM database'. My first step is to check which tables take up most space, for which I use the SQL script at the end of this post. The most common large tables, and reasons for them are:

1. AsyncOperationBase: This stores all asynchronous operations, and can get very large if completed ones aren't cleared out. You have to explicitly set the registry values AsyncRemoveCompletedWorkflows and AsyncRemoveCompletedJobs. These 2 values are covered in separate KB articles (http://support.microsoft.com/kb/968755 and http://support.microsoft.com/kb/957871). To clear existing records, use the SQL script in http://support.microsoft.com/kb/968520/
2. ActivityMimeAttachment: This stores email attachments
3. AnnotationBase: This stores other files (those associated with Notes)
4. ActivityPointerBase and/or ActivityPartyBase; These store activities and the associated parties respectively. Although individual record sizes aren't typically large, these tables typically have the largest number of records

For no.s 2 to 4 I've not given a way of clearing out space, as this would involve deletion of business data.

SQL Script
I normally use the following SQL Script to find what objects take up most space in a SQL database. It doesn't take account all possible storage in all SQL databases, but works fine for an MSCRM database

select OBJECT_NAME(object_id), SUM(reserved_page_count) / 128 as ReservedMB, sum(used_page_count) / 128 as UsedMB, MAX(row_count)
from sys.dm_db_partition_stats
group by object_id
order by SUM(reserved_page_count) desc

A view of records owned by the user's business unit

2010-08-06T07:27:00.003+01:00

Bubbling something up from an answer I gave on the Dynamics CRM forums. Although it may not seem obvious within Advanced Find, it is possible to create a view that displays records owned by a user's business unit.

The following gives the contacts owned by the current user's BU - strictly it's composed as 'contacts owned by a business unit that contains the current user'

Contacts
Owning Business Unit
Users (Business Unit)
User Equals Current User

Advanced Developer Extensions - an update

2010-05-12T12:12:00.003+01:00

Just a quick note to say that I've updated my post on the Advanced Developer Extensions. Shan McArthur of AdxStudio (who developed these extensions) was kind enough to provide extra information about these extensions, which I've now incorporated into the original post

CRM SDK 4.0.12 and the Advanced Developer Extensions

2010-05-11T11:23:00.006+01:00

The CRM 4.0.12 SDK has recently been released. Normally an SDK update is not particularly significant, but in this case it includes some major enhancements, which come under the banner of 'Advanced Developer Extensions'. David Yack has already posted a quick how-to on the CRM Team Blog; rather than duplicate that, this post is intended to cover the scope and limitations of the new extensions as I see them, and how they differ from the original CRM programming model.

What are the new extensions ?
It seems to make sense to split the new features into 2; the 'Advanced Developer Extensions' (whcih has been helpfully shortened to Microsoft xRM), and the Portal Accelerator (aka Portal Developer). The Portal accelerator uses Microsoft xRM, but I think it is otherwise best treated separately. So, for this post I'll concentrate on Microsoft xRM.

Architecture of Microsoft xRM
Although Microsoft xRM appears to provide a whole new programming model, it can be considered as essentially a (rather big) wrapper around the existing SDK assemblies (microsoft.crm.sdk and microsoft.crm.sdktypeproxy). So, although your code would not directly use the classes in the SDK assemblies, the communication with CRM is still ultimately done via the CRM web services, and is subject to the same limitations (e.g. limitations of the FetchXml query syntax). Another consequence of this is that you do not need to change any of your network configuration to use these extensions.

Changes to the Programming Model
This is where it gets interesting; the new extensions provide a whole new programming model, which can affect pretty well all of the code you use to communicate with CRM. The major changes as I see it are:

You can use strongly-typed classes for the CRM entities. Although you can do this with the existing SOAP CRM web services, up till now you needed to use the DynamicEntity class with the SDK assemblies
Native .Net types are used for attributes - e.g. int? rather than CrmNumber. Note that nullable types (e.g. int?, rather than int) as used
The connection information is covered within a DataContext class, which effectively replaces the use of the CrmService instance
Data modifications can be cached locally, then submitted as a batch, using the DataContext.SaveChanges method
The extensions provide additional methods to update lookup values. These can work off either the lookup attribute name (e.g. customerid) or the relationship name (e.g. contact_customer_accounts). The extensions also provide methods to retrieve related records, which avoids the need to use QueryExpression or QueryByAttribute
You can use LINQ queries to retrieve data from CRM, rather than building QueryExpression instances
The DataContext and LINQ technologies allow direct data-binding with .Net user interface controls

Use of Strongly-Typed classes
As with the SOAP web services, you can use strongly-types classes for system and custom entities. Superficially the process for setting this up differs from the SOAP web services, although the underlying idea is pretty similar. With these extensions, you use a supplied tool (CrmSvcUtil.exe) to connect to a CRM server. This tool will generate the class definitions for all CRM entities into one output code file which you'll add into your .Net project. Ultimately, this process is very similar to what happens behind the scenes when you create a Web Reference to the SOAP web services. The main internal difference is that the generated classes with these extensions maps down to the DynamicEntity class, but this is hidden from you.

You can still use a generic class with these extensions rather than strongly-typed classes. With the extensions it is ICrmEntity, rather than DynamicEntity.

Native .Net Types
Native .Net Types are used instead of Crm-specific types. The extensions use the nullable versions of the types (e.g. int?, rather than int) so that you can still identify null values (which was one of the main original reasons for the Crm-specific types). For picklist and status attributes the extensions provide an additional Label attribute (e.g. customertypecodeLabel) with the appropriate text, whereas for lookup attributes you can get the related entity via a property that has the relationship name (e.g. price_level_accounts).

DataContext class
This replaces the need for the CrmService instance, and handles the selection of authentication type, passing credentials and management of the CrmAuthentication token. All connection-related information (server URL and port, organisation name, authentication type and credentials) can be specified in one connection string. The extension code includes logic to read connection string information from the application config file. Overall, this should greatly simplify deployment across different environments and authentication types.

The DataContext exposes an IOrganizationService instance, which looks to combine the underlying ICrmService and IMetadataService instances. This allows use of any of the more specific CrmService messages (e.g. InstantiateTemplate), or MetadataService messages.

Interestingly the constructor for the DataContext can take an IContextService or IWorkflowContext instance, but not an IPluginExecutionContext instance. This implies that the extensions can work within a custom workflow activity, but are of limited use within plugins. See below for more on this.

Batch Updates
It looks to me as though any changes submitted via the DataContext will be submitted as a batch via the SaveChanges method. This won't provide any transactional support, as this is not possible within the Crm Web Services. Overall, I think this approach is due to the design patterns used in the Microsoft data-related classes, and I'm pretty neutral as to whether this offers more benefits or drawbacks. If you do develop with these extensions, I'd bear the following in mind:

I could imagine developers forgetting to include the SaveChanges method, and I can't see (though I've not tested this) a way that the extension code would throw an error is pending changes were discarded
There seems to be no means to control the behaviour if an individual data operation fails, and the documentation doesn't currently describe the default behaviour here. To get such control you need to call SaveChanges for each individual data modification

Handling lookups and relationships
I've not looked at this in great detail, but I see the greatest benefit is the simplicity of retrieving related records with methods such as GetRelatedEntities to get child and many-many entities, and GetRelatedEntity to get a parent entity.

Use of LINQ queries
Again, not an area I've spent much time with. In the first instance I see this as most useful for developers that are already familiar with the LINQ query syntax, but I generally encourage the use of standard technologies (such as LINQ) in preference to application-specific technologies (such as QueryExpression). My expectation is that you should expect developer productivity gains with use of LINQ instead of QueryExpression, but I've not spent enough time on this to get good metrics. It should be emphasised that the LINQ query ultimately maps down to a QueryExpression, and hence you are as limited in the scope of queries as you currently are.

Data Binding
This really comes as a direct benefit of the use of standard .Net data classes, and LINQ. You can directly bind UI components like the ASP.Net GridView control to the results of a LINQ query. Oddly, there doesn't seem to be an example of this in the main crmsdk4.chm help file, but the following code extract from the 'advanced_developer_extensions_-_walkthrough_webapp.docx' document in the SDK shows how easy and powerful this can be:

var crm = new XrmDataContext("Crm");
ContactsGrid.DataSource = crm.contacts.Where
(c => c.emailaddress1.EndsWith("@example.com"));
ContactsGrid.DataBind();

Limitations of the Advanced Developer Extensions
There's a lot of very good stuff in the areas mentioned above, which could have significant benefits on developer productivity. However, I don't think you can immediately replace all existing .Net code (even if you wanted to), as there are some areas that I don't think these extensions reach (yet?). Note that the following list is based on my investigations so far, and may include features that do exist, but which I've missed in my brief analysis so far.

Plugins. As mentioned above, the constructor for the XrmDataContext class has no overload that takes an IPluginExecutionContext. I also can't see a way to manipulate a DynamicEntity (e.g. from InputParameters) within these extensions, so I don't think they are viable for use in plugin code
IFD (aka SPLA) impersonation. AD impersonation is supported, but I can't see the equivalent of the CrmImpersonator class. I need to do some further tests on this to see if IFD impersonation can work with these extensions; if not it would restrict the deployment scenarios for web extensions

It's also worth pointing out that the major benefits of the extension code relate to standard data operations (Create, Retrieve, Update, Delete). You can still use the additional messages (e.g. InstantiateTemplate, Merge) of the CrmService and MetadataService through these extensions, but you'll need to use the standard SDK types

My understanding is that these extensions originated as a basis for developing portal-style applications against CRM, so in that context it is not at all surprising that there are areas where the code can't reach. It'll be interesting to see how this changes, both with CRM 4 and CRM 5.

What Next ?
What next, indeed. The timing of this release is interesting, coming relatively late in the life of CRM 4 as the most recent version of CRM. It's still a little too early to know what will happen with CRM 5, but it would be logical to expect that these extensions will continue to work with future versions of CRM. The more interesting question is whether we will continue to have more that one programming model for CRM 5 (e.g. a native interface, and extensions such as these which form a wrapper around the native interface), or whether these 2 models will start to merge together.

CRM 4.0 UR 10

2010-04-13T17:38:00.001+01:00

It's that time again; a new Update Rollup is out. See
http://support.microsoft.com/?kbid=979347
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5da4d352-1d3f-4426-9c17-b256c30afdb9

I think the most significant fix in this rollup is the fix for metadata in new Organisation databases, as described at http://support.microsoft.com/kb/980627/ . This fixes a bug that started in UR 7, in which a newly created organisation (i.e. created after rollup 7 or higher had been installed) missed some SQL objects and permissions. The 2 symptoms I'd met of this were:

Missing stored procedures for cascading relationships. This gives the error: SqlException: Could not find stored procedure 'dbo.p_CascadeCollectAssign'
Missing permissions on the FilteredActivityPointer view. This gives the error: the SELECT permission was denied on the object 'FilteredActivityPointer', and is described here

A bit of SQL - finding CRM attributes with no data

2010-03-13T13:38:00.003Z

Over time I've been asked to review various existing CRM implementations. One thing I often like to check is whether there are any attributes that are not in use. To this end I created the stored procedure at the end of this post.

The stored procedure lists the attributes in an entity along with how many records contain data in that attribute. The parameters in the procedure allow you to limit the attributes to custom attributes, and to specify a maximum count of values (above which the attribute data won't be output).

Notes:

The procedure as written would be created in the MSCRM organisation database. You could create it in a different database, and add the databasename.schemaname. prefixes if you want
The procedure uses the supported route of querying the filtered view for an entity. If you have SQL dbo permission, and don't mind deleted records being included in the counts, then you can run the query against the base view, which is considerably faster. To do this, remove the string 'Filtered' from the procedure definition

Use the following SQL to create the stored procedure:

create proc pExcCheckColumnValues @entityName nvarchar(64), @customOnly bit = 0, @maxThreshold int = null
as
declare @attrName nvarchar(50), @sql nvarchar(1024)
declare cur cursor fast_forward for
select a.name from attribute a
join entity e on a.entityid = e.entityid
where e.name = @entityName and a.IsLogical = 0
and ((@customOnly = 1 and a.IsCustomField = 1) or isnull(@customOnly, 0) = 0)
create table #tmp (EntityName nvarchar(64), AttributeName nvarchar(50), ValueCount int)
open cur
fetch next from cur into @attrName
while @@fetch_status = 0
begin
set @sql = 'insert #tmp select ''' + @entityName + ''', ''' + @attrName + ''', count(*) from Filtered' + @entityName + ' where ' + @attrName + ' is not null'
+ case when @maxThreshold is not null then ' having count(*) <= ' + cast(@maxThreshold as nvarchar) else '' end
exec (@sql)
fetch next from cur into @attrName
end
close cur
deallocate cur
select * from #tmp
go

To give an example of how to use the procedure, the following will return all custom attributes of the account, where no more than 10 account records have a value in this field:

exec pExcCheckColumnValues @entityName = 'account', @customOnly = 1, @maxThreshold = 10

Service Principle Names and Security

2010-03-11T19:14:00.002Z

I've recently been working with CRM and Reporting Services in environments where AD delegation has been required, and hence it's been important to configure Service Principle Names (SPN).

A good overview of SPNs and how they are set can be found in this document for CRM 3. Although some of the steps in that document have been superseded by the use of the CRM 4.0 Reporting Services connector, the document is still a good reference for the main aspects of setting up SPNs.

There are two further considerations that are not covered in the above document though; ensuring Kerberos is used, and getting permission to modify SPNs.

Ensure Kerberos is used
There are 2 Windows authentication mechanisms: NTLM (aka Challenge/Response) and Kerberos. Only Kerberos supports delegation. By default, Kerberos will be used if the client supports it (this setting is known as 'Negotiate'), but this behaviour can be changed.

For web applications hosted in IIS (such as CRM and Reporting Servers 2005), this is controlled by the NTAuthenticationProvider setting in the IIS metabase. Note that this can be set at a few different levels, and you'll need to check them all to determine the current configuration. When you install CRM, the Environment Diagnostics Wizard requires that Negotiate is the default setting, though it could have been changed after install.

Reporting Services 2008 does not use IIS, and the authentication settings are configured in the rsreportserver.config file. Information about these settings can be found here in SQL Server 2008 Books Online.

Permissions required to modify SPNs
It's all very well knowing what SPNs need to be created or modified, but you may not have permission to do this, especially in high security environments. SPN data is stored in Active Directory, hence you need appropriate permissions in Active Directory. There are two approaches that you can take to get the SPNs setup if you don't have permission:

Ask a Domain Administrator to grant you the permissions. The second half of this technet article describes the permissions you need, and how they are assigned
The individual service accounts can be granted permission to create their own SPNs. This is more applicable to SQL Server SPNs, but can be used by any code that can register it's own SPN. This is well described here

Update Rollup 9

2010-02-12T21:01:00.002Z

It's that time of every other month again. A new update rollup is available; number 9 is available here. There don't seem to be any particularly important fixes this time, unless you're in one of the time zones affect by this bug, which is now fixed

CRM 4 Update Rollup 8 is out

2009-12-18T09:39:00.002Z

See http://support.microsoft.com/default.aspx?kbid=975995 and http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c53b2916-6b93-4092-bdd3-a394c96ca000

I've not had a chance to test it yet. One welcome inclusion is an optimisation to the Import Organisation process (http://support.microsoft.com/kb/977867/ ), though interestingly you have to add a registry value to enable the optimisation.

There's also appraently a fix to the 'dsMain' error with Report Wizard, as described here

Analysing SQL Queries in CRM environments

2009-12-03T17:03:00.003Z

I've another post here on the Dynamics CRM team blog describing some techniques I use to make effective use of SQL Profiler and SQL Management Studio. This post also references a tool I published here on the MSDN Code Gallery

New Code Gallery resource - sp_executesql parser

2009-12-02T10:49:00.003Z

I've added a new code resource to the MSDN code gallery. This is a simple tool to parse calls to sp_executesql. The reason for it is that the SQL tools cannot show the execution plan for calls to sp_executesql, which is a pain when doing performance analysis of these queries. So, I created a tool that replaces the manual editing process.

Soon, I'll post a more detailed description of SQL performance analysis for CRM implementations, which will reference this tool.

Attachment Download code in UR 7 - an update

2009-11-13T17:56:00.002Z

I've updated my original post here to reflect a suggested fix posted by Microsoft on the MSDN Forums here.

Despite my major reservations with how the problem arose, credit is due to Microsoft for responding as quickly as I think they possibly could (even if it doesn't always seem that way).

My reservations relate to disagreements on the need for the change made in UR7 (I don't think it increases security at all), and also to how MS allowed a change that broke supported extensions. It'll be interesting to see what happens with UR 8...

Socket Exhaustion when accessing CRM web services

2009-11-13T11:40:00.002Z

When submitting a large number requests to the CrmService web service, you may occasionally get the error 'Only one usage of each socket address (protocol/network address/port) is normally permitted'. The reason for this is 'socket exhaustion', which is excellently described here. You are at risk of this happening when over 4000 web requests are submitted within 4 minutes - I find this most commonly on data imports, but have recently met it during heavy user activity on a live CRM system.

There are 2 solutions:

As suggested in the link above, add registry values to give a much wider range of socket addresses. Note that you need to restart the server for these registry changes to take effect
Set the UnsafeAuthenticatedConnectionSharing and PreAuthenticate properties of your CrmService (or MetadateService) proxy to true. This will allow your web request to reuse the same socket

Field Level Security in CRM 4.0 - MS White Paper

2009-11-12T09:46:00.004Z

The Microsoft Dynamics CRM Engineering for Enterprise (MS CRM E2) team have released a detailed white paper describing the supported options for implementing field-level security in CRM 4.0. It is available for download at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=471f8670-47b3-4525-b25d-c11a6774615c .

As I was one of the document's technical reviewers, I may be a bit biased in my praise of the document, however one option is missing from it. As a Microsoft document, it concentrates on the supported approaches, but there are also unsupported approaches, such as that taken by c360, which uses an HttpModule to modify the html sent to the client. Personally I dislike this approach, as there is a potentially heavy performance overhead, and it is fragile with respect to future changes in hotfix rollup. However, it is another option that could be considered, and is unfortunately not mentioned in the white paper.

UR 7 breaks Attachment Download code, and how to fix it

2009-11-06T09:52:00.004Z

* 2009-11-13. I've updated this post to reflect some proposed SDK changes published by Microsoft to the MSDN Forums here. I'll probably update this post again for reasons I'll come to later, but I think it's worth making an initial update now *

It was going to happen at some point; a CRM hotfix rollup that broke existing supported customisation code. Update Rollup 7 adds some security changes that apply to the Attachment/download.aspx page. As a result the example code documented here in the SDK no longer works.

If you try the code, the user gets the error message "Access Denied, Invalid Operation". If you enable tracing, you see a message like the following:
"Message: INVALID_WRPC_TOKEN: Validate WRPC Token: WRPCTokenState=Invalid, TOKEN_EXPIRY=4320, IGNORE_TOKEN=False, TOKEN_KEY=b1Nd0byfEd6+gwAZuez7cauYiRnfHuMLAquFY1Ks22dHgxZiW5IrxSobkv9aVfbC, ErrorCode: -2140991221"

The fundamental problem is that CRM now expects additional parameters on the query string that contain a Token, and there is no way for us to generate that token. There's a significant question about why this change was made, but I'll leave that for another time.

The recommended workaround is the use the web services directly as described at http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/2f46a3c4-1123-49dd-804e-2787ef367986, which I expect to make it into the SDK soon. This is close to a like-for-like replacement for the original SDK code in that it is the type of code you might write for a console application.

However, a popular use of download.aspx was to provide any easy client-side link to download an attachment, rather than doing it programmatically with .Net code. To get equivalent behaviour you'll need a custom .aspx page that gets the uses similar code to that above, but writes it the response stream using Response.BinaryWrite. I'm going to have to write this code soon, and when I do I'll post it on the MSDN Code Gallery, and link to it here; in the meantime if anyone has to write it themselves the other thing you should do is pass information on the Response-Header to specify the filename. From memory this involves something like:

Response.AddHeader("content-disposition","attachment; filename=" + outputFile);

SSIS Packages and Excel Data on 64 bit machines

2009-11-05T19:56:00.001Z

We do a fair amount of integration work between CRM and Excel using SSIS, and it's all got a bit harder with 64 bit machines. The main issues are:

There is no 64 bit OLEDB provider for Excel
So the packages cannot be executed by 64 bit code. This meant that the programmatic route for running packages with the classes in the Microsoft.SqlServer.Dts.Runtime namespace no longer works. The error you get is typically unhelpful - 'AcquireConnection method call to the connection manager "EXCEL: Source" failed with error code 0xC00F9304'. You can also get error code 0xC020801C.

The workaround is to run a package using the 32 bit version of dtexec.exe, which should be installed in Program Files (x86)\Microsoft SQL Server\100\DTS\Binn. This is also the fix if scheduling packages with the SQL Agent; your job steps need to be of type CmdExec instead of SSIS Package, and you have to learn the command line syntax of dtexec.

Overall, something of a pain, but I've not yet found anything I can't do with dtexec.exe and its command line parameters.

The Registry settings for the 32 bit OLEDB provider for Excel have moved
There are a several registry settings that you may need to make the OLEDB provider for Excel work, as described here and here. When running a package in 32 bit mode on a 64 bit server, you find that it uses registry keys in a different location.

Values that were in HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel are now in HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

Note also that the paths to the providers have changed, so for example "Win32"="C:\Windows\system32\msexcl40.dll" becomes "Win32"="C:\Windows\syswow64\msexcl40.dll"

Although my emphasis here has been on Excel providers, and SSIS, the issues and resolutions can apply to other OLEDB providers and environments, as there are several OLEDB providers that are not expected to have 64 bit releases (the Exchange OLEDB provider, and the FoxPro one are 2 that I'm aware of)

Data Integration using Reports

2009-09-22T17:06:00.003+01:00

I've another post on the CRM Team Blog site at http://blogs.msdn.com/crm/archive/2009/09/22/data-integration-a-reporting-approach.aspx

This is another post on design patterns (like this one), looking initially at potential data integration approaches and key issues, then covering some specifics on the reporting approach.

Announcements: Update Rollup 6 and DynamicsWorld

2009-09-03T10:18:00.002+01:00

As announced elsewhere, CRM 4.0 Update Rollup 6 has been released. Our initial assessment is that there is one significant fix – to resolve the Invalid Email Address error introduced in rollup 4. For those customers where the decision whether or not to apply a rollup is decided on a per rollup basis, this fix will typically be significant enough to apply the rollup.

I was interviewed for the Dynamics World web site recently, so I figured I should add a link to the interview here

Windows NT Authentication Provider - it can work at several levels

2009-08-18T10:58:00.003+01:00

One of the diagnostics checks performed by the CRM Environmental Diagnostics Wizard prior to installation is to confirm that the CRM web site has the NTAuthenticationProvider set to 'Negotiate,NTLM'. I've never entirely worked out why this is necessary, especially as I've subsequently changed the Authentication provider after installation with no adverse effects (* though see below), but that's not the point of this post.

The main point is that the NTAuthenticationProvider attribute can be viewed or set at several levels via the adsutil.vbs script. This is reasonably well documented in the Technet article referenced from the EDW help file. However, what is not made clear is that the attribute can be set at one of 3 levels. Each of the following commands does something slightly different:

cscript adsutil.vbs set w3svc/WebSite/root/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/WebSite/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/NTAuthenticationProviders "Negotiate,NTLM"

The third command is noticeably different, in that it sets the attribute at the server level, rather than the web site level. I can't see any particular reason for different behaviour between the first 2 commands, but it does affect the CRM EDW. As far as I can tell, you need to use the syntax in the second command to satisfy the EDW, whereas unfortunately the Technet article uses the syntax in the first command.

* Re what Authentication Provider to use. I've happily run a single server implementation of CRM using NTLM authentication, but I expect Kerberos may be necessary in a multi-server implementation

Reports on CRM Privileges - Update to view by User

2009-08-07T16:51:00.002+01:00

I've added 2 reports to the resource on the MSDN Code Gallery. These show cumulative privileges by user across their roles

Hidden CRM Privileges

2009-08-04T16:39:00.002+01:00

Not all CRM privileges are visible within the CRM User Interface. I recently spent some time investigating what privileges exist in CRM, and how the privilege information is stored in the MSCRM database.

The results of these investigations have been posted on the Microsoft Dynamics CRM Team Blog. I also created a couple of reporting services reports to display the privilege data by role. These are available on the MSDN Code Gallery

Some undocumented CRM attribute types

2009-07-30T09:48:00.015+01:00

I was working on some custom plugin registration tools recently, and came across some undocumented attribute types. Several customisation entities (e.g. PluginAssembly, PluginType, SavedQuery) have an attribute called CustomizationLevel which has a value of 0 for system data, and 1 if it is custom or customised (according to the SDK).

The SDK documentation states this attribute is of type CrmNumber, so when writing a QueryExpression that selects only records with a CustomizationLevel = 1, it would seem reasonable to use something like:

qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, 1));

However, this gives the error “Condition for attribute 'customizationlevel': expected argument(s) of type 'System.Byte' but received 'System.Int32' " (code 0x80040203 - Invalid Argument). Digging deeper I found that the field for the CustomizationLevel is stored in SQL as a tinyint (i.e. a single-byte integer), and that the AttributeTypes table in CRM has a corresponding AttributeType of tinyint.

So, despite the attribute being identified as a CrmNumber, any condition expressions need to pass values as a single-byte integer, not the documented four-byte integer. This is easily done by using the following:

qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, (Byte) 1));

Being somewhat nosey, I thought to see what other attribute types there were. These can be easily found with the following SQL query:

Select * from AttributeTypes

In addition to tinyint, 2 similar types caught my attention – smallint and bigint (SQL Server data type names are not that imaginative). Following on from this, the following SQL query lists attributes of these types, which may cause similar problems to those above:

select e.name as Entity, a.name as Attribute, at.description as [Type]
from attribute a join entity e on a.entityid = e.entityid
join attributetypes at on a.attributetypeid = at.attributetypeid
where at.description in ('tinyint', 'smallint', 'bigint')
order by at.description, e.name, a.name

This yields the following results. The bigint attributes aren’t a concern, as these 2 attributes aren’t available via the CRM platform, but you could encounter some of the smallint types on UserSettings, in which case I expect you’d have to cast values to Int16.

Entity	Attribute	Type
AsyncOperation	sequence	bigint
Subscription	completedsyncversionnumber	bigint
Organization	tokenexpiry	smallint
UserSettings	advancedfindstartupmode	smallint
UserSettings	timezonecode	smallint
UserSettings	timezonedaylightday	smallint
UserSettings	timezonedaylightdayofweek	smallint
UserSettings	timezonedaylighthour	smallint
UserSettings	timezonedaylightminute	smallint
UserSettings	timezonedaylightmonth	smallint
UserSettings	timezonedaylightsecond	smallint
UserSettings	timezonedaylightyear	smallint
UserSettings	timezonestandardday	smallint
UserSettings	timezonestandarddayofweek	smallint
UserSettings	timezonestandardhour	smallint
UserSettings	timezonestandardminute	smallint
UserSettings	timezonestandardmonth	smallint
UserSettings	timezonestandardsecond	smallint
UserSettings	timezonestandardyear	smallint
Organization	fiscalyeardisplaycode	tinyint
Organization	tagmaxaggressivecycles	tinyint
Organization	trackingtokeniddigits	tinyint
OrganizationUI	customizationlevel	tinyint
PluginAssembly	customizationlevel	tinyint
PluginType	customizationlevel	tinyint
SavedQuery	customizationlevel	tinyint
SdkMessage	customizationlevel	tinyint
SdkMessageFilter	customizationlevel	tinyint
SdkMessagePair	customizationlevel	tinyint
SdkMessageProcessingStep	customizationlevel	tinyint
SdkMessageProcessingStepImage	customizationlevel	tinyint
SdkMessageProcessingStepSecureConfig	customizationlevel	tinyint
SdkMessageRequest	customizationlevel	tinyint
SdkMessageRequestField	customizationlevel	tinyint
SdkMessageRequestInput	customizationlevel	tinyint
SdkMessageResponse	customizationlevel	tinyint
SdkMessageResponseField	customizationlevel	tinyint

Reading RDL Definitions directly from a ReportServer database

2009-07-23T18:19:00.000+01:00

Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

MS Dynamics Top 100 Influential People

2009-07-05T18:51:00.001+01:00

Sometimes it's good to come last. I was honoured and flattered to find my name at no. 100 (that's me, last on the list) in “The Microsoft Dynamics Top 100 Influential People 2009" at DynamicsWorld.

It's good to see several other CRM MVPs ahead of me on the list - you can find out who they are here

CRM 4 Update Rollup 5

2009-07-03T18:40:00.002+01:00

As several others have announced, CRM 4 Update Rollup 5 has been released. There are a couple of notable points to make:

As with update rollups 3 and 4, the majority of the fixes have not been previously released as an individual hotfix. This is different from rollups 1 and 2, which were mostly a collection of previously released fixes. In general, I think that having fewer individually released fixes is a positive thing, but that has to be traded off against the frequency of rollups (currently about every 6 to 8 weeks), which is difficult to keep up with. Across our customers, we have deployments covering 5 different rollup levels, plus some customers on CRM 3, which needs a fair bit of management, especially with regard to schema compatibility
So, schema compatibility. Apparently, UR5 schemas cannot be imported into versions prior to UR2, but also, pre-UR2 schemas cannot be imported into UR5. I've got some investigation to do on this, but the most important factor for me is being able to use one development environment that can be used to export (and import if necessary) schemas to all rollup versions of CRM 4. Up to now I've rarely applied rollups to development environments, but it looks like from now on, UR2 will be a minimum requirement for a development environment. Either way, it makes life harder than it should be.

Report Server location and the error: The specified path is not a metabase path

2009-06-16T19:38:00.002+01:00

One of the ‘known’ issues with using CRM 4.0 and ESRV0008 is that CRM does not realise that SQL 2008 Reporting Services hosts the ReportServer application outside of IIS. Hence, when you try and edit the ReportServer URL in CRM Deployment Manager, or install CRM, you may get the error "The specified path is not a metabase path". As I said, this is a known issue, and a workaround is described at http://support.microsoft.com/kb/957053 (issue no. 6 in the article).

The problem though is that this workaround doesn’t always, well, work. I tried this, with no success whatsoever. Eventually, I went back to some other issues CRM had had with identifying IIS metadata in the past with CRM 3.0 (e.g. http://support.microsoft.com/kb/916164 ). What I had to do was temporarily change the IIS bindings so that the default web-site (i.e. web-site with ID=1) was on port 80. As long as this is the case when you edit the Report Server URL, then everything works. Fortunately, all I was doing was editing an organisation in an existing deployment, and could arrange downtime to temporarily change web-site bindings; if you’re installing a new CRM deployment it may be harder to play with web-site bindings. For completeness, the steps to make this work were:

In IIS Manager, create a new web-site with bindings that match those of the ReportServer URL, as per issue 6 in KB 957053 (link above)
In IIS Manager, change the current web-site bindings so that the web-site with ID=1 is on port 80 with no host-headers (if necessary turn off your phone and don’t check email while people try and complain they can’t access their web-sites)
In CRM Deployment Manager, disable the organisation, edit the organisation an put in the ReportServer URL you want and continue till the organisation has been updated successfully. Enable the organisation
Back in IIS Manager, change the web-site bindings back to how they were, turn your phone back on, and find someone/something else to blame for the temporary outage

Why Microsoft code things assuming the default web-site is on port 80 is beyond me. Oh well.

During my investigations I found out a few more things:

McAfee anti-virus software hooks into some popular alternate ports (in this case 8081), and it’s not easy to diagnose conflicts when you put ReportServer and McAfee on the same port
Configuring multiple bindings for the ReportServer URL works fine
'All' the above does is make sure that CRM populates the ReportServer with the correct folders, reports, data sources and permissions. It is possible to set all this up manually and get it to work, but it’s very tedious and you may need to change the Guids that identify the reports within either CRM or Report Server

CRM Design Patterns - and back on the MSCRM Team Blog

2009-05-22T16:16:00.003+01:00

I seem to be using my invitations to post on the MSCRM Team blog as a prompt to publish posts - I've posted a starter post on Design Patterns.

I'm intending this to be the first in a series of posts on Design Patterns for MS CRM. I'm using the term 'Design Pattern' deliberately to echo the same term used in object-oriented design, though I'm not intending the posts to be particularly formal or prescriptive. If you're interested in the more formal ideas of design patterns, wikipedia as good a place as any to start.

My reasons for investing time in this are two-fold:

I don't like having to do thinkgs more than once. When designing MS CRM solutions for a customer probably about 90% of the design decisions I make now are wholly or partly based on a previous, similar design for another customer. A lot of this information is in my head; the intention of building design patterns is to create a structure to be able to communicate this information to others
MS CRM solutions are a mix of out-of-the-box functionality, and specific customisations and extensions. This gives a lot of options, and a lot of additional considerations beyond traditional ground-up development, or out-of-box deployment, and I think there's a lot of new and interesting design work that can be done in this environment

Using the IIS Logs to get CRM Usage information

2009-04-20T10:17:00.002+01:00

I've been posting on the Microsoft CRM Team blog again.

The article describes how to make use of the IIS logs to get useful information about who is accessing CRM and when.

This technique uses some SQL objects (tables, view and functions), and a reporting services report. The source code for these is available on the MSDN Code Gallery

CRM and SQL 2008 Whitepaper

2009-02-02T21:46:00.002Z

MS have released an interesting white paper about how you can use some of the new SQL 2008 features with CRM - http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

The row compression feature, along with the sparse columns, look very useful for reducing the number of pages needed by a table, which is important for ad-hoc advanced finds which use able scans, and pre-filtered reports.

Filtered indexes can be very useful, especially on the statecode for relatively volatile entities. For instance, the ability to use filtered indexes on open cases or opportunities could make a big difference to overall index storage

I need to do some more testing before I'm convinced on the benefits of the page compression. I could see it working for large quantities of relatively static data, such as a large account or contact table, but I think the processing overhead on data modification (which is always hard to quantify) means that I'd be reluctant to use it on more volatile data (e.g. opportunities, cases, activities).

Office 2007 System Driver - errors in SSIS if you don't have them

2009-01-26T18:22:00.004Z

A quick post, as I didn't find any others out there giving all the details you may need. SQL 2008 SSIS supports the Office 2007 file formats, but requires the Office 2007 System Driver.

If you deploy a package onto a machine without these drivers, you get errors like the following when the component is validated:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered
Hresult: 0x80040154 Description: "Class not registered"

Installing the Office 2007 System Driver should fix this.

Some SQL Snippets

2009-01-23T15:55:00.004Z

Over time (don't ask how long – suffice to say I first used Microsoft SQL Server on OS/2) you pick up a fair amount of useful SQL Server knowledge. This post is intended to be a random collection of snippets that I use and remember, and I expect to add to the post periodically as I encounter further uses for SQL knowledge. So, in no particular order...

Finding SQL objects that contain a particular string
The definition of SQL objects can be accessed via the sys.syscomments view in the SQL database, and can be queried. The following example returns the name of objects that contain 'Test' somewhere within the definition. The object_name function is a quick way to get the name of an object from its id – the other way is to join to the sys.objects view.

select distinct object_name(id) from sys.syscomments where text like '%Test%'

Note that this only works if the SQL object definition was not encrypted with the WITH ENCRYPTION option

Granting Permissions to a set of objects
I've yet to find a good user-interface in SQL for setting permissions on a set of objects quickly, so I tend to use SQL commands. The following procedure shows how to use a cursor to iterate through a set of objects and execute a dynamically-built GRANT statement on them

declare cur cursor fast_forward for

select name from sys.objects
where type = 'V' and name like 'vw_%' -- Get all views, beginning vw_

declare @obj sysname, @sql nvarchar(2000)
open cur
fetch next from cur into @obj
while @@fetch_status = 0
begin
set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'
-- grant select permission to public
exec (@sql)
fetch next from cur into @obj
end
close cur
deallocate cur

Outputting stored procedure information to a table
There are cases when you might want to use the results of a stored procedure in a table structure for future processing. There's not an EXECUTE INTO statement but you can use INSERT ... EXECUTE. You can also use this with dynamically constructed SQL, using EXECUTE (@sql). The following example uses both EXECUTE syntaxes, andshows how to iterate though the names of 'tables' from a linked server – this is used to query Excel spreadsheets where there is a dynamic range of identically structured worksheets

create
table #excelsheets -- Store names of spreadsheets in Excel
( TABLE_CAT sysname null
,TABLE_SCHEM sysname null
,TABLE_NAME sysname not null
,TABLE_TYPE sysname null
,REMARKS nvarchar(255) null )

insert #excelsheets execute sp_tables_ex 'EXCELDYNAMIC'
-- EXCELDYNAMIC is a linked server

create table #tmp
-- Temporary storage of data, so results can be output as one result set
( TABLE_NAME sysname
,[Month] int
,[Target] decimal(10,2) )

declare cur cursor fast_forward
for select TABLE_NAME from #excelsheets
declare @tbl sysname, @sql nvarchar(4000)
open cur
fetch next from cur into @tbl
while @@fetch_status = 0
begin
-- Build dynamic SQL statement. It would be nice to pass the statement as a parameter to OPENQUERY, but that's not permitted
set @sql = 'Select ''' + @tbl + ''' as TABLE_NAME, [Month], [Target] FROM EXCELDYNAMIC...[' + @tbl + ']'
insert #tmp exec (@sql)
fetch next from cur into @tbl
end
-- Cleanup and output results
close cur
deallocate cur
select * from #tmp
drop table #tmp
drop table #excelsheets

Viewing CRM 4.0 Reports in Report Manager

2009-01-22T12:49:00.003Z

Some of my previous posts (e.g. http://mscrmuk.blogspot.com/2008/01/scheduling-and-emailing-reports-with.html) were written based on CRM 3.0. Most of the reporting principles are similar for CRM 4.0, but the reports are configured slightly differently within Report Manager. The key differences are:

The CRM items are hidden by default in Report Manager. If you browse to the OrganisationName_MSCRM folder you won't see the hidden items. To see them, click on the 'Show Details' button on the right of the toolbar
The CRM 4.0 reports are stored in a sub-folder called 4.0. Click on this folder to see the reports
CRM 4.0 specifies the ReportID Guid as the report name, and the friendly name is now in the Description fields

CRM 4.0 Update Rollup 2

2009-01-15T22:02:00.003Z

Update Rollup 2 for CRM 4.0 has been released, which is a pretty fast turnaround after the recent Update Rollup 1. Ive not had time to look at many of the fixes yet, but Ill add to this post if anything interesting crops up.

Information about the hotfix: http://support.microsoft.com/default.aspx?kbid=959419
Download link: http://www.microsoft.com/downloads/details.aspx?familyid=aa671769-61e9-45c4-919f-c88199aa4241&displaylang=en&tm

CRM Error Codes

2009-01-06T09:19:00.003Z

Something that's cropped up on a couple of CRM forums recently is how to get more information about a CRM error code. Error codes can be displayed in one of three formats:

Decimal, e.g. -2147206371
Hex with the 0x prefix - e.g. 0x80043B1D
Hex without the prefix - e.g. 80043B1D

The CRM SDK documentation has a reasonable set of information about each error here, but only refers to error codes in the last of the listed formats (e.g. 80043B1D). To convert the second format to the third format, just remove the 0x prefix.

The easiest way to convert the first (decimal) format is to use the Windows calculator utility (calc.exe), as follows:

Open the calculator in scientific mode
Check the Decimal (Dec) option is set
Paste in the error code - e.g. -2147206371
Select 'Hex' to convert it to Hex, this will then look like: FFFFFFFF80043B1D
Remove the leading FFFFFFFF. You can then find the resulting code, e.g. 80043B1D, in the error codes

Invalid Argument error when importing unpublished entities

2008-12-14T19:45:00.004Z

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

More CRM MVPs

2008-11-24T16:37:00.001Z

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

Using the Reporting Services Execution Log with CRM 4.0

2008-11-19T16:19:00.004Z

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

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

2008-11-17T17:51:00.003Z

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

2008-10-22T15:35:00.002+01:00

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:

Building / Deploying a plugin registration tool
Registering the assembly
Adding referenced assemblies to the GAC
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:

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'

2008-10-15T14:19:00.004+01:00

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

2008-10-11T10:11:00.001+01:00

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

2008-10-10T10:05:00.002+01:00

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

2008-09-14T16:21:00.001+01:00

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

2008-09-09T15:34:00.005+01:00

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

2008-09-08T08:44:00.004+01:00

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

2008-09-04T11:47:00.004+01:00

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:

Identify the SQL query that takes the time - I use CRM tracing for this - http://support.microsoft.com/kb/907490
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:

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
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

2008-09-01T12:50:00.003+01:00

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:

The Plug-in example code now uses the DynamicEntity class as recommended
The code for use of ImportXml, ExportXml and PublishXml looks like it now passes all required XML nodes
There are now instructions for setting up a web reference in Visual Studio 2008
The PrependOrgName client-side function is now documented (and hence supported)

Hiding System Views in CRM 4.0

2008-08-31T10:57:00.001+01:00

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

2008-08-29T10:46:00.000+01:00

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:

Via the Target InputParameter
Via an Image Entity registered on the step
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:

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
A PostImage Entity is available in the post-stage, and a PreImage Entity in the pre-stage only
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
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

2008-07-31T15:46:00.003+01:00

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

2008-06-27T08:52:00.005+01:00

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

2008-06-20T16:30:00.009+01:00

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

Reports in IFrames - Updated for CRM 4.0

2008-06-19T11:45:00.003+01:00

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.

'This entity is already locked' message

2008-06-11T17:47:00.003+01:00

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.

Differences between Workflow and Plugins in CRM 4.0

2008-06-03T19:09:00.009+01:00

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

CRM SDK version 4.0.5 has been released

2008-06-02T15:22:00.001+01:00

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

Blog Update - Links to Code Gallery Resources

2008-05-22T09:33:00.005+01:00

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

2008-05-22T09:15:00.003+01:00

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

Some Plugin Tools

2008-05-21T08:13:00.001+01:00

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

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

2008-04-21T15:02:00.005+01:00

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.

Email Templates with Custom Entities

2008-04-03T17:37:00.002+01:00

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

Workflow assembly. Trying to make a Lookup parameter optional

2008-03-03T18:11:00.000Z

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}

MSDN Code Gallery

2008-01-30T19:00:00.000Z

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'

Scheduling and emailing reports with CRM

2008-01-26T14:13:00.000Z

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

CRM 4.0 Error with ISV pages: MultipleOrganizationSoapHeaderAuthenticationProvider

2008-01-14T16:07:00.000Z

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:

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)
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):

CRM 4.0 - Installation with no Internet Access

2007-12-20T21:49:00.000Z

The current set of downloadable files for CRM 4.0 do not include some pre-requisite software (including .Net 3.0 framework and Visual C++ redistributable). If this software is not already installed and the setup program can't find it locally, the CRM setup program can download the files from the Internet, which is fine, if you have Internet access from the CRM server...

If you don't have Internet access you can pre-install these components, except the Visual C++ redistributable which cannot be reliably detected, so the CRM setup program will always try to install it.

Therefore, without Internet access, you have to have a local copy of the Visual C++ redistributable locally. Philip Richardson's blog describes where to put these files, so I won't bother repeating it here, but there are a couple of extra things that it might help to know:

CRM setup checks the version of the local copy of the redistributable, and if it's not what it expects, will again try to connect to the Internet. You can identify this scenario from the setup log; the message is "Error when checking signature", though oddly it's marked as an Info message
CRM setup only checks for local files when it starts. So if, like me, you get to the screen where it expects to download the files, realise it won't be able to, then copy them locally, then go Back, Forward, it won't help. Instead, exit setup completely and start over again

As to Internet access from the CRM server, in most circumstances this can be achieved from a VPC image with the use of more than one network adapter. See my earlier post

CRM 4.0 - Client script changes from CRM 3.0

2007-12-20T14:49:00.000Z

This post is intended to be a dumping ground that I'll update as and when I, or my colleagues, find issues that may affect the upgrade of CRM 3.0 implementations with client script.

RemoteCommand - removal of some options
In CRM 3.0 it was possible to make unsupported use of the client-side RemoteCommand function to make server-side calls, e.g. the following got the current CRM user id

var cmd = new RemoteCommand("SystemUser", "WhoAmI", "/MSCRMServices/");
var res = command.Execute();
if (res.Success)
return res.ReturnValue.UserId;

What this did was make a request to the SystemUser.asmx (1st parameter) file in the MSCRMServices (3rd parameter) directory. Almost all of these files have been removed in CRM 4.0, so code like that above will fail.

I can't see any obvious equivalent calls to make in CRM 4.0, but as the use of RemoteCommand is unsupported, I wouldn't expect there to be. My preferred option has always been to write a server-side wrapper around calls the CrmService web service, and call them directly using the XmlHttp ActiveX object, thus keeping within supported functionality.

CRM 4.0

2007-12-20T14:41:00.000Z

As the RTM code for CRM 4.0 is now available (http://philiprichardson.org/blog/post/CRM-40-Downloads.aspx), I figure it's time to start recording some of the changes from CRM 3.0, and associated upgrade issues.

As such I'm going to create a few posts that I'll add to as we find out more about CRM 4.0; the first ones will be on client script changes, server code and upgrade issues

Stored procedures in SSIS; the need for nocount

2007-12-20T12:19:00.000Z

I've been doing a lot of SSIS work lately, and met an odd problem when using stored procedures in an OLEDB source. The general idea was to have a stored procedure that did some data modification, then finished with a SELECT statement to output logging information.

When previewing the OLEDB source everything was fine, but running the package would give the following error in the pre-execute phase:
'A rowset based on the SQL command was not returned by the OLE DB provider'

It took a while to work out, but eventually the problem came down to the way that SQL Server returns information about the number of rows affected. The resolution is to put the following SQL statement at the start of the stored procedure defnition to prevent output of the number of rows:

SET NOCOUNT ON

After that it was all fine.

Automating Local Data Query Creation

2007-12-14T17:29:00.000Z

I've been invited to post on the Microsoft Dynamics CRM Team Blog again. This time I posted about how to automate the creation of the local data groups that specify what data each user of the Laptop client take offline.

Rather than repeating the text, you can read the article here

I've posted sample code for the article on CodePlex

Providing different default views for different CRM users

2007-12-03T11:44:00.000Z

Or, how to modify CRM grid pages via client script.

CRM provides 2 standard ways to add custom client script into the CRM application; via form events, and through menu items and buttons in ISV.Config. Neither of these allows you to write code to control the default views for CRM entities

However, there is a way round this. The approach is to create a host HTML (or ASP .Net) page that has no interface components itself, but contains the CRM page in an IFrame. This will appear exactly the same as the standard CRM page, but allows you to write client script in your host page that controls the CRM page, and hence change which view is displayed.

Note that this approach has to be considered as unsupported, as it involves programmatic control over a picklist outside of a CRM form.

Building the host page
There are 2 aspects of the host page; providing the HTML to host the IFrame without adding extraneous borders or padding, and adding client script that runs once the IFrame has loaded.

The following HTML shows how to host the IFrame:

<body style="margin:0" onload="Init();">
<iframe onreadystatechange="ors();" id="ifr" src="about:blank" width="100%" height="100%" frameborder="0" leftmargin="0">
</body>

In this example I’m creating a generic page that can host most CRM grid pages, and I’m setting the src property of the IFrame programmatically:

function Init()
{
var etc = getQS('etc');
if (etc != null)
{
document.all.ifr.src = '/_root/homepage.aspx?etc=' + etc;
}
}

This src is the standard way to display most entity grids, with etc as the object type code of the entity.

I’m using the onreadystatechange event to determine whether the IFrame has loaded:

function ors()
{
if (event.srcElement.readyState == 'complete')
{
var sView = getQS('view');
if (sView != null)
SetView(sView);
document.all.ifr.style.visibility = 'visible';
}
else
{
document.all.ifr.style.visibility = 'hidden';
}
}

This checks the readyState property of the IFrame, which will equal ‘complete’ when the IFrame contents have loaded, and the IFrame contents after they have been modified.

The following code shows how to modify which view is selected:

function SetView(sView)
{
var ifDoc = document.frames['ifr'].document.all; // access IFrame contents
var oSel = ifDoc['SavedQuerySelector']; // picklist control to select view
if (oSel != null)
{
var v = GetSelectValue(oSel, sView);
if (v)
{
oSel.DefaultValue = v;
oSel.DataValue = v;
oSel.FireOnChange(); // need to fire this event to apply changes
}
}
}
function GetSelectValue(oSel, sText) // helper function to select item in picklist
{
for (var i=0;i<osel.options.length;i++)
{
if (oSel.options[i].text == sText) return oSel.options[i].value;
}
}

The code uses another helper function to access parameters passed on the query string. This function is oversimplified in that it doesn’t cope with all possible encoding issues, but is sufficient for this example:

function getQS(name)
{
var ret = '';
if (window.location.search != null && window.location.search.length > 1)
{
var aQS = window.location.search.substring(1).split('&');
if (aQS != null)
for (var i=0;i<aQS.length;i++)
if (aQS[i].indexOf(name + '=') == 0)
ret = aQS[i].substring(name.length + 1).replace('%20', ' ');
}
return ret;
}

Putting this all together, we have a page that will display an entity grid, and change the default view, based on 2 query string parameters. For example

defaultViewChanger.htm?etc=1&view=Active%20Accounts

Will display the account grid, and set the view to the Active Accounts view (%20 is the encoding of a space, which is not permitted in a url).

Setting different default views for different users
We now have a page that can programmatically change the default view. There are 2 ways this can be used to provide different default views to different users: a programmatic way that identifies the current user (and probably team or role membership) and hence determines the default view, or via permissions in SiteMap.

I’m not intending the cover the programmatic route in detail here; my preference is to convert the page to an ASP .Net page that identifies the current user and their role, and populates the parameter to SetView in server code; an alternative approach in client code can be found here.

Another approach is to make use of the Privilege element in SiteMap to display different navigation links to different sets of users based on their permissions, as described in more detail here. Let’s assume we have 2 groups of users who want different default views of the account entity, group A (who are members of security role ‘roleA’) want to see My Active Accounts, and group B (members of ‘roleB’) who want to see Active Accounts.

We can create a dummy entity in CRM called exc_acctsecurity and grant roleA write rights on the exc_acctsecurity entity, and grant roleB assign rights. Then we can modify SiteMap, replacing the SubAreas for the account entity to the following:

<SubArea Id="nav_accthostA" Title="Accounts" Url="http://server/defaultViewChanger.htm?etc=1&view=My%20Active%20Accounts" Icon="/_imgs/ico_18_1.gif">
<Privilege Entity="exc_acctsecurity" Privilege="Write" />
</SubArea>
<SubArea Id="nav_accthostB" Title="Accounts" Url="http://server/defaultViewChanger.htm?etc=1&view=Active%20Accounts" Icon="/_imgs/ico_18_1.gif">
<Privilege Entity="exc_acctsecurity" Privilege="Assign" />
</SubArea>

Note the use of XML encoding (& instead of &).

One drawback of this approach is that anybody in the System Administrator role will see both SubAreas.

A complete example of the HTML and SiteMap can be found on CodePlex.

Viewing all files in CRM related to an account

2007-12-03T10:56:00.001Z

I've recently published a second CRM related reporting services project on codeplex. This one illustrates how to get a list of all files related to a CRM account. This includes files attached directly to the account or via related objects (e.g. contacts, opportunities, cases), and also includes both file and email attachments.

Most of the work is done in the SQL, which I've written as a set of SQL views for ease of reading, rather than embedding all the SQL in the report.

The project also illustrates how to link to CRM objects, within a report, as the report contains reporting services actions to display the CRM entity forms, and also actions on each file to link to the CRM file download dialog.

This report is written so as to take parameters from a ISV.Config button, as described here

External access from a Virtual Environment

2007-11-23T07:25:00.000Z

We do almost all our CRM development and testing within virtual images. This confers many benefits, but raises issues if you need connect the image to the outside world. One example is when registering software; another is when developing callouts / plugins that communicate with external web services.

The major issue arises with a self-contained image that is a domain controller in its own domain, as well as a SQL server and CRM server. Active Directory depends on DNS, so the image has to be its own DNS server, which forces it to have a fixed IP address, which we place on separate IP private IP subnet (e.g. 192.168.x.0). This essentially prevents this IP address and adapter being used externally. With just the one adapter and IP address, you can’t change the IP address to a dynamic one (e.g. from DHCP), because within the image that breaks DNS, which breaks AD, which breaks CRM.

So, the solution; add a separate network adapter to the image, using an external IP address. With Microsoft Virtual PC or Virtual Server, the steps to take are:

Turn off the image
Add a 2nd network adapter to the image, and associate it with the physical adapter in the host machine
Start the image, and set the IP address on the 2nd adapter to something that will work on the external network; either dynamically assigned via DHCP, or to a reserved static address

This resolves the IP addressing issue, and if you only need to connect to an external resource by IP address than that’s as much as you need. However, you may still have a DNS problem. I’ve not investigated all scenarios, but I have met cases when, even if the 2nd adapter is configured for with external DNS server, the image still uses its own DNS server for all DNS requests. In this scenario the simplest solution is to configure a forwarder for the virtual DNS server, so that it forwards all DNS requests that it cannot resolve to your known external DNS server. This can be configured with the DNS management snap-in.

Using permissions to personalise SiteMap

2007-11-21T07:38:00.000Z

CRM allows modification of the CRM application navigation structure via SiteMap. This is normally considered to provide one customised navigation structure for all CRM users, with the only difference between what one user sees and what one of their colleagues sees is what they have permission for.

For most entries in SiteMap, whether they are displayed or not depends on the user’s permission on the corresponding entity. For example, if a user does not have Read privileges at any level on the invoice entity, they will not see invoices in SiteMap. However, SiteMap allows more explicit control over what privileges are required to display any link; this is done via the Privilege element.

The following extract from the default SiteMap illustrates how this is used:

<SubArea Id="nav_managekb" ResourceId="Homepage_KBManager" Icon="/_imgs/ico_18_126.gif" Url="/cs/home_managekb.aspx" Client="Web"> <Privilege Entity="kbarticle" Privilege="Read,Write,Create" /></SubArea>
This specifies that the Knowledge Base link in the Services Area should only be displayed to users who have Read, Write and Create privileges on the kbarticle entity.

Privilege elements are interpreted based on these rules:

In one Privilege element, the user must have all privileges listed in the Privilege attribute
The level of privilege doesn’t matter; user level is sufficient
Multiple Privilege elements can be specified for one SubArea; in this case the user needs to fulfil the permissions for every element

There is no requirement for the Privileges element(s) to be related to the entity (or external link) that is displayed.

Using the Privileges element
Given this, it is perfectly reasonable to create multiple SubArea elements in SiteMap that would show similar (but not identical) information, but only display one of them to any given user, based on their permissions. For example, you may have created custom portal pages, but the customer service staff should see a different page from that seen by marketing staff. Showing a different page for each set of users could be achieved with the following entries in SiteMap:

This works providing that support users can create cases (schema name incident) but not campaigns, while the opposite applies to marketing users.

This approach can work well assuming suitable privileges can be found to distinguish between different groups of users, but what if there aren’t any suitable permissions ?

Creating dummy entities for permissions
The Privileges element can refer to custom entities, and there is nothing to stop us creating one or more entities solely for the purposes of personalising SiteMap. For example, we could do the following:

Create an entity called dummy_security
Grant one role, ‘Support Role’, Write privilege on this entity
Grant another role, ‘Marketing Role’, Assign privilege on the entity
Ensure users are added to the appropriate role
Change the above snippet from SiteMap to the following:

This would give users the correct link, without relying on existing privileges that may change based on changing job roles, or changing business requirements.

The one thing to watch for is to make sure you don’t grant Read or Create privilege on this new entity; this will ensure it will not appear to users anywhere in the normal CRM application. Note that members of the System Administrator role will necessarily see all the SubAreas, because they will automatically have full privileges on all entities.

Redeploying callouts by recycling the application pool, rather than IISRESET

2007-11-15T22:46:00.001Z

When redeploying a callout on CRM 3.0, it is considered necessary to run IISRESET to unload the callout assembly from memory, and hence allow you to copy a new version of the dll. This can have several issues:

After an IISRESET, it takes long enough to reload / rerender CRM data to be annoying when frequently testing and redeploying versions of a callout assembly
If other users are using CRM, there will be an outage during the IISRESET
IISRESET affects all other web applications on the server

To minimise the impact of 1) above, Michael Höhne's local debugging mechanism is an excellent solution in a lot of environments.

However, there are still the occasions when the callout has to be redeployed on to the CRM server. There's not much that can be about 2), although the following suggestion helps a bit.

We can do something about 3). A callout is loaded into the process for the CRM application pool (CrmAppPool), and it is only this application pool that needs to be recycled, rather than the whole of IIS. An application pool can be recycled via the IIS management console, but there is an easy way to script this, as follows:

Open a command prompt
Change directory to %Windir%\system32
Run the following command:

cscript iisapp.vbs /a CrmAppPool /r

This will just recycle the application pool, after which you can copy the new callout assembly

Syntax issues using SQL Execute As and Revert statements

2007-11-15T21:28:00.000Z

SQL Server 2005 provides a means to switch user context via the EXECUTE AS statement. This can be very useful when querying CRM Filtered views, as they use the SQL user context to determine what data to return and if queried using the CRM service account they return no data. An example of how this can be used in CRM callouts can be found here. I've also used a similar approach when running SSIS packages programmatically from a web application, as ASP .Net impersonation doesn't work properly in SSIS packages.

However, when testing this further, and checking the behaviour of REVERT, I found this only works correctly if you separate your SQL statements with semi-colons. Take the following 2 examples:

Execute as user='crmdom\admin'
select * from filteredaccount
revert

declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql)
revert

Of these 2, the former appears to work, but the REVERT statement doesn't get processed, and the latter gives an 'incorrect syntax' error. But, if you add a semi-colon after the statement before REVERT, then they work fine:

Execute as user='crmdom\admin'
select * from filteredaccount;
revert

declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql);
revert

Now, that is not what I expected in SQL syntax. I always thought semi-colons were an optional statement separator, but apparently not -though I expect this is a bug rather than by design.

Note, this behaviour happens on build 2047 of SQL 2005, whereas it works without semi-colons on build 1399, but that doesn't stop me using them as a matter of course with impersonation.

Changes to client-side code for CRM 4.0

2007-11-12T13:43:00.000Z

Following on from looking at server-side code changes for CRM 4.0, here's what I know so far about client-side code changes. Again, this is based on the CTP3 code so may soon become out-dated. Another caveat is that it is based on investigation of the CRM client files, and not on the SDK documentation (which at time of posting is incomplete); hence, some of what I cover may well be undocumented and hence unsupported.

Calling CRM web services directly
This has never been a favourite approach of mine, as the creation of the SOAP data in client-code seems too fragile; I prefer to create server-side web pages or services to abstract the CRM web service. However, I know many people who make direct SOAP requests, and there is another consideration - if customising a deployment on Windows Live you will not be permitted to deploy server web code, so you will have to make direct requests.

If you do make direct requests, you will need to pass the new CrmAuthenticationToken within the SOAP header. Rather than generating this yourself, there is a new global method for this:

GenerateAuthenticationHeader();

CRM 4.0 now provides a proper web service interface for offline use. This can be accessed from client-side code via the http://localhost:2525 location.

New Global Variables
CRM 4.0 provides the following documented global variables related to the multi-language and multi-tenant features:

USER_LANGUAGE_CODE - Code (LCID) of the current user's language
ORG_LANGUAGE_CODE - Code (LCID) of the base language
ORG_UNIQUE_NAME - Current organisation name

New Object Properties and Methods
Note, everything in this section may be undocumented and hence unsupported. As you may know, the crmForm and field objects are implemented as HTML components (.htc files) in the /_forms/controls directory. A comparison between the CRM 3.0 and CRM 4.0 versions show the following changes:

All CRM 3.0 properties and methods seem intact, even the undocumented ones. I've not tested this exhaustively, but all current code works fine.

The crmForm object has the following new members:

fireSaveEvent
HideField - I hope this will become supported, as it will avoid direct DHTML manipulation
VerifyFieldIsSet
BypassValidation - a property to tell CRM not to validate fields

The Lookup control has more members, mostly to do with the new auto-resolve functionality:

AutoResolve and ResolveEmailAddress boolean properties
AreValuesDifferent method allows comparison between the lookup contents and a supplied array of values

The Picklist control exposes the underlying properties of the select HTML element, SelectedIndex and SelectedOption.

Across these, and the undocumented members from CRM 3.0, what I see as most significant is the extent to which they will be supported in CRM 4.0. We shall see.

Changes to code to use CRM 4.0 web services

2007-11-09T17:02:00.001Z

I've recently been rewriting some of our products for CRM 4.0, and here's a summary of the server-side code changes required when converting code from using the CRM 3.0 web services to the CRM 4.0 web services. Note that this is based on the CTP3 release of CRM 4.0, so may not be accurate for release code.

Use of the CRM 3.0 web service
CRM 4.0 retains the CRM 3.0 web service, along with an implementation of the CRM 3.0 microsoft.crm.platform.callout.base.dll. This allows you, as far as I can tell, to retain code written for CRM 3.0 and have it work with CRM 4.0. So far I've found 2 limitations to this:

If you use the CRM 3.0 Web Service, you will access the default organisation, and will not be able to use multiple organisations. You will also have to use AD authentication
A few of the CRM 3.0 Web Service methods have been deprecated and won't work. The main class of these are those used to manage workflow

If you look in the directory structure of CRM 4.0 you'll see the following:

mscrmservices\2006\CrmService.asmx (used for CRM 3.0 web service access)
mscrmservices\2007\CrmService.asmx (used for CRM 4.0 web service access)

However this post is mostly about the changes you need to make if you are targetting the CRM 4.0 we service.

The CrmDiscoveryService service
The first major differences with respect to the web services with CRM 4.0 is that there is a third web service, the CrmDiscoveryService. This is primarily used to enumerate the organisations in use and provide the connection information for them. This service will be necessary in a multi-tenanted environment; whether you need to use it in a single-organisation on-premise implementation is open for debate (which deserves a separate post).

Setting up the CrmService service
The first key difference is the use of the CrmAuthenticationToken property. This became necessary to accommodate multi-tenancy and the new authentication type. It also replaces the CallerIdValue property. With the Enterprise edition of CRM 4.0 you have to set the OrganizationName; I don't know yet if it will be required in the Professional edition.

So, combining the 2 services above, you have 2 ways to setup your CrmService instance. Both assume the variable Org contains the organisation name. First, without using the CrmDiscoveryService:

CrmService svc = new CrmService();
svc.Url = Server + "/MSCRMServices/2007/CrmService.asmx";
svc.Credentials = System.Net.CredentialCache.DefaultCredentials;
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = Org;
svc.CrmAuthenticationTokenValue = token;

Alternatively, you can use the CrmDiscoveryService to retrieve the data required for the CrmService instance:

CrmDiscoveryService disco = new CrmDiscoveryService();
disco.Url = Server + "/MSCRMServices/2007/AD/CrmDiscoveryService.asmx";
disco.Credentials = CredentialCache.DefaultCredentials;
RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)disco.Execute(orgRequest);
foreach (OrganizationDetail orgdetail in orgResponse.OrganizationDetails)
{
if (orgdetail.OrganizationName == Org)
{
  CrmAuthenticationToken token = new CrmAuthenticationToken();
  token.AuthenticationType = 0;
  token.OrganizationName = Org;
  crmservice = new CrmService();
  crmservice.Credentials = CredentialCache.DefaultCredentials;
  crmservice.CrmAuthenticationTokenValue = token;
  crmservice.Url = orgdetail.CrmServiceUrl;
}
}

This code uses AD authentication. To use another authentication type, change the url to the CrmDiscoveryService, and change the AuthenticationType property of the CrmAuthenticationToken.

Using the CrmService service
The good news is, almost everything stays the same as before, and there is no need to rewrite code, other than to pass in an Organisation name. However, if you serialise and deserialise entities you'll have to pay attention to the Xml namespaces, as some classes retain the CRM 3.0 Xml namespaces, and some have a new Xml namespace. For example, from the generated proxy code:

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://schemas.microsoft.com/crm/2006/WebServices")]
public partial class DynamicEntity : BusinessEntity

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://schemas.microsoft.com/crm/2007/WebServices")]
public partial class account : BusinessEntity

Fortunately for me, the DynamicEntity is only class that I regularly serialise, and the namespace hasn't changed.

Using the MetadataService service
Here, your code has to change, as this has moved over to the Request model with an Execute method. So, for example, the following CRM 3.0 code:

EntityMetadata em = mdSvc.RetrieveEntityMetadata(EntityName.account.ToString(), EntityFlags.IncludeAttributes);

will need to be rewritten as:

RetrieveEntityRequest req = new RetrieveEntityRequest();
req.LogicalName = EntityName.account.ToString();
req.RetrieveAsIfPublished = true;
req.EntityItems = EntityItems.IncludeAttributes;
RetrieveEntityResponse resp = (RetrieveEntityResponse) mdSvc.Execute(req);
EntityMetadata em = resp.EntityMetadata;

Which is longer, but does illustrate one new feature, the ability to choose between the published and the unpublished information.

Another big difference relates to the way labels are used, which comes from the multi-language features. Again, some CRM 3.0 code:

PicklistAttributeMetadata pam = mdSvc.RetrieveAttributeMetadata(EntityName.account.ToString(), "industrycode")
foreach(Option o in pam.Options)
if (o.OptionValue == 1)
Console.Writeline(o.Description);

Which becomes:

RetrieveAttributeRequest req = new RetrieveAttributeRequest();
req.EntityLogicalName = EntityName.account.ToString();
req.LogicalName = "industrycode";
req.RetrieveAsIfPublished = true;
RetrieveAttrbiuteResponse resp = (RetrieveEntityResponse) mdSvc.Execute(req);
PicklistAttributeMetadata pam = (PicklistAttributeMetadata) resp.AttributeMetadata;
foreach (Option o in pam.Options)
if (o.Value.Value == 1)
Console.Writeline(o.Label.UserLocLabel.Label);

The Option.Value property is now of type CrmNumber, and there is a Label property that allows access to all localised labels.

The MetadataService also now supports modification to be metadata, but as that's new functionality I'm not going to cover it here.

Using Microsoft assemblies
With CRM 3.0, the only Microsoft CRM .Net assembly you were permitted to reference was microsoft.crm.platform.callout.base.dll. With CRM 4.0, there are 2 assemblies you can reference:

Microsoft.Crm.Sdk.dll
Microsoft.Crm.SdkTypeProxy.dll

At the moment I've not investigated them fully, as they are not well documented, and subject to change prior to release. The main new feature though is the implementation code for a PropertyBag, which is a collection class that allows, among others, easy manipulation of the properties of a DynamicEntity:

Microsoft.Crm.Sdk.DynamicEntity de = new Microsoft.Crm.Sdk.DynamicEntity();
de.Name = EntityName.account.ToString();
de.Properties.Add(new StringProperty("name", "My Co"));
if (de.Properties.Contains("name"))
Console.Writeline(de["name"].Value);

This uses a bit of helper code from the Sdk for a partial class for StringProperty with a constructor that takes 2 parameters.

Other changes
One minor annoyance is that both the CrmService and MetadataService classes have classes with the same name, e.g. AttributeType. This means you cannot be as indiscriminate with your use of using or imports statements as I like to be.

Retrieving Billable time and activity time for cases

2007-11-09T12:21:00.000Z

When a case is resolved in CRM, the user can specify the amount of Billable Time spent on the case, and they can also see the total activity time spent on the case:

Both sets of information can be useful, and are necessarily stored in the CRM database. However, how you retrieve these figures is not well documented.

Where the data is stored
Data about the case is stored in 3 main entities (note that I'm using the schema name for the entities, and in the schema a case is called an incident):

incident: This is the main case entity, which stores the information you see on the main form, but does not include and of the duration information listed above. The statecode field identifies whether a case is open, resolved or cancelled.

incidentresolution: This is strictly a special type of activity, and stores the user entered data in the Case Resolution dialog. The 'Resolution Type' is stored in the statuscode field, and the user-entered 'Billable Time' is stored in the timespent field.

activitypointer: All activities related to a case have an activitypointer record. The duration of each closed activity is stored in the actualdurationminutes field.

There are a couple of additional complexities to the data storage:

It is possible for a case to have been resolved, then reactivated, and resolved again. This results in 2 incidentresolution records, so you need to identify the correct one. I always go by the most recent
The incidentresolution entity is a type of activity, hence has an associated activitypointer record. Fortunately, this record has no value for the actualdurationminutes, so doesn't affect any calculations of total activity time. However, you do have to be aware of this if you want a count of the number of activities

Retrieving the data
Case data is normally retrieved via reports, and hence SQL queries. The following query shows how to extract the correct Billable Time and Total Activity Time:

select i.incidentid, i.title, ir.actualend
, max(timespent) as BillableTime
, isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime
from filteredactivitypointer a
join filteredincident i
on a.regardingobjectid = i.incidentid
left outer join filteredincidentresolution ir
on i.incidentid = ir.incidentidand ir.statecode = 1 -- Resolved incident resolutions
and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1) -- Most recent ir activity
where i.statecode = 1 -- Resolved cases
group by i.incidentid, i.title, ir.actualend

A few comments on the query:

I'm only returning resolved cases in this example, but I've used an outer join to incident resolution to make it easy to change the i.statecode filter to include unresolved cases
I'm using isnull on the actualdurationminutes field to return 0 if there is no activity time
If you add any other fields from the case, or related entities (e.g. account), you'll need to add them to the group by clause as well as the select clause, or join to them in a separate query

Displaying the data in CRM
As the billable time and activity time are not stored in the incident entity, they cannot be displayed directly on the case form or in any views. To display this information in the case form, my preferred approach is to create a report using the above SQL query, and display this in an IFrame on the case form, using the techniques described in Writing Reports to display in IFrames

In CRM 3.0 there is no useful way to display this data in a view. In CRM 4.0 this should change, though it may not be as easy as you'd like; CRM 4.0 allows display of related data in a view, but it looks like this is only data from parent entities (e.g. the account to which a case belongs), not the summarised data from child entities that we're discussing here. However, the new PlugIn model allows intercepting of the view data, and should permit such display of child data with a bit of code. This is something I'll be posting on in the future, but it will have to wait till the CRM 4.0 SDK documentation is finalised.

Workflow problems on CRM 3.0 with .Net framework 2.0

2007-11-08T13:24:00.000Z

If you have .Net framework 2.0 or higher it can adversely affect the Workflow engine. This can give one or more of the following problems:

Workflow rules do not run (errors can be found in the event log, pointing you at workflow monitor, but nothing useful can be found in workflow monitor)
Bulk import operations never go beyond pending, even though the workflow service is running
When starting Workflow Manager on the CRM server you get an error message 'Workflow Manager cannot start...configuration file is corrupted'
You cannot attach to the CrmWorkflowService to debug workflow assemblies with Visual Studio .Net 2003

This can be because the Crm Workflow Service is running with the 'wrong' version of the .Net Framework. To correct this, create a text file with the following (case-sensitive) text:

Save the file as CrmWorkflowService.exe.config in the \Microsoft CRM\Server\bin directory. Then restart the CRM Workflow Service.

Using reports in IFrames and from ISV.Config

2007-11-08T13:04:00.001Z

This blog is not the first one I've posted on; I've previously been invited to post on the Microsoft CRM Team blog, and I posted this article Writing Reports to display in IFrames and from ISV.Config

It seems unnecessary to repeat the post here, so I won't. However I will soon be posting some additional reports that build on this technique.

It has to start somewhere - an introduction

2007-11-08T12:54:00.000Z

It seems rude not to start with an introduction, so here goes. I've been working with MS CRM as my main technical focus for over 3 years, and became a Microsoft CRM MVP in April 2007. My main online involvement with the 'CRM community' thus far has been in the CRM newsgroups and forums, mostly helping with individual issues. This is the next step...

There are several reasons for starting this blog, the main one is to give me more space to cover fuller solutions or ideas than can be covered in the forums. These will be biased towards MS CRM customisation and development, but will doubtless stray into related technologies - SQL, Reporting Services, SharePoint, SSIS and .Net development - as well as infrastructure issues as I find them.