Friday, 18 December 2009

CRM 4 Update Rollup 8 is out

See and

I've not had a chance to test it yet. One welcome inclusion is an optimisation to the Import Organisation process ( ), 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

Thursday, 3 December 2009

Analysing SQL Queries in CRM environments

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

Wednesday, 2 December 2009

New Code Gallery resource - sp_executesql parser

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.

Friday, 13 November 2009

Attachment Download code in UR 7 - an update

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

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:
  1. 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
  2. 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

Thursday, 12 November 2009

Field Level Security in CRM 4.0 - MS White Paper

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 .

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.

Friday, 6 November 2009

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

* 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, 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);

Thursday, 5 November 2009

SSIS Packages and Excel Data on 64 bit machines

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)

Tuesday, 22 September 2009

Data Integration using Reports

I've another post on the CRM Team Blog site at

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.

Thursday, 3 September 2009

Announcements: Update Rollup 6 and DynamicsWorld

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

Tuesday, 18 August 2009

Windows NT Authentication Provider - it can work at several levels

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

Friday, 7 August 2009

Reports on CRM Privileges - Update to view by User

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

Tuesday, 4 August 2009

Hidden CRM Privileges

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

Thursday, 30 July 2009

Some undocumented CRM attribute types

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 as Entity, 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,,

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.


Thursday, 23 July 2009

Reading RDL Definitions directly from a ReportServer database

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

Sunday, 5 July 2009

MS Dynamics Top 100 Influential People

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

Friday, 3 July 2009

CRM 4 Update Rollup 5

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

  1. 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
  2. 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.

Tuesday, 16 June 2009

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

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

  1. 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)
  2. 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)
  3. 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
  4. 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

Friday, 22 May 2009

CRM Design Patterns - and back on the MSCRM Team Blog

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:
  1. 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
  2. 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

Monday, 20 April 2009

Using the IIS Logs to get CRM Usage information

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

Monday, 2 February 2009

CRM and SQL 2008 Whitepaper

MS have released an interesting white paper about how you can use some of the new SQL 2008 features with CRM -

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

Monday, 26 January 2009

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

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:

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.

Friday, 23 January 2009

Some SQL Snippets

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
set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'
-- grant select permission to public
exec (@sql)
fetch next from cur into @obj
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

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
-- 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
-- Cleanup and output results
close cur
deallocate cur
select * from #tmp
drop table #tmp
drop table #excelsheets

Thursday, 22 January 2009

Viewing CRM 4.0 Reports in Report Manager

Some of my previous posts (e.g. 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:
  1. 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
  2. The CRM 4.0 reports are stored in a sub-folder called 4.0. Click on this folder to see the reports
  3. CRM 4.0 specifies the ReportID Guid as the report name, and the friendly name is now in the Description fields

Thursday, 15 January 2009

CRM 4.0 Update Rollup 2

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:
Download link:

Tuesday, 6 January 2009

CRM Error Codes

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:
  1. Decimal, e.g. -2147206371
  2. Hex with the 0x prefix - e.g. 0x80043B1D
  3. 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:

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