Mitch Milam's Dynamics CRM and xRM Discussions

In case you didn't know, CRM 3.0 has a real issue should it find the same email address in more than one location ( i.e., Contact, Account, System User, etc ). 

For example, let's say you have a company of 20 people and you create an Account for your company within CRM and add each of the employees as Contacts. You populate their contact information as you normally would for any Contact.

Without knowing it, you've just broken CRM.

Here is the result of above operation:

The problem lies in the fact that CRM can't resolve the email address to a single individual so rather than prompting you to make the decision, it will merely create a message alerting you. Not to the fact that it found more than one email and it doesn't know which one to pick, but to the fact that it can't resolve the email address.

Not exactly clearest warning message I've ever seen, and one which that took me a while to locate the cause.

I'm not exactly sure this is the best way to handle the situation because unless the user knows what is going on, it can cause a whole lot of confusion.  In the above example, what it appears that CRM is telling you, is that it doesn't know who you are? Clicking on the email address in red will allow you to select the correct email acount and solve the issue, but it will make the user say, "but that was the same address that was there before." And they would be correct.

So, the moral to the store is to keep the emails unique within the system.

The Problem: 

And how do I find out if I have any duplicate emails?

The Solution: 

With the SQL Script that I have attached to this article.

How it works:

1. Unzip CRMDupCheck.zip and you will find CRMDupCheck.sql.
2. Open SQL Enterprise Manager.
3. Navigate to the MSCRM database.
4. Open Query Analyzer.
5. Open CRMDupCheck.sql
6. Click the Execute button. Assuming you have duplicate emails, the output should look something like this:

Download it here.