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

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

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

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

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

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

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