Microsoft CRM Customization secrets second edition

Understanding the Gap Between the CRM SDK and Real‑World Requirements

When building extensions for Microsoft Dynamics CRM, developers often rely on the SDK to perform most data operations. The SDK is comprehensive, but it doesn't always cover every scenario, especially when you need to intervene earlier in the message flow or perform bulk updates that would be cumbersome through the API. One common situation is handling incoming email that arrives in Exchange before the CRM Exchange Connector has had a chance to associate it with an entity. In that case, you need to examine the email’s subject, check for a GUID, or otherwise determine if the sender’s address matches an existing lead, account, or contact. Once you know where the email belongs, you must create a closed activity email in CRM and place it in the general queue. The SDK provides a way to create an activity, but there is no direct method for marking it as closed in a single call. This gap forces developers to use lower‑level techniques such as direct SQL updates.

Direct SQL programming in Dynamics CRM is a topic that sparks debate. Official Microsoft documentation warns against it because bypassing the SDK can lead to data integrity issues, and Microsoft’s support policy excludes this scenario. However, for highly specialized tasks that the SDK can’t perform, many experienced developers turn to direct updates. The key is to understand which fields govern an activity’s state and how to set them correctly. When done responsibly, a direct SQL update can be both safe and efficient.

In this article, we walk through a practical example that illustrates both approaches: first, we use the SDK to create an email activity with the appropriate parties and owner, then we switch to a direct SQL update to mark the activity as closed. The code snippets are written in C# and use the legacy 1.2 SDK, which is still common in many on‑premise deployments. While newer SDK versions provide additional abstractions, the concepts remain the same.

To start, let’s describe the workflow you’ll need to implement. When an email arrives, you must:

Inspect its subject for a CRM GUID.
If no GUID is present, look up the sender’s email address in the CRM database.
Determine the matching entity type (lead, account, or contact).
Create an email activity that is attached to the entity.
Close the activity immediately after creation.
Implementing this logic requires both SDK calls and direct database manipulation. The following sections provide the detailed code and explain the rationale behind each step.

Before you start, make sure you have the following prerequisites:
- Access to the Dynamics CRM server’s database and the ability to run SQL commands against it.
- A reference to the CRM 1.2 SDK assemblies in your project.
- Proper error handling and logging, as the code interacts with external services.
 With those items in place, you can begin writing the functions that will perform the tasks described above.
 
 Creating a Closed Email Activity with the SDK and SQL
 
 The first step is to construct a helper method that will create the activity using the SDK. The method takes several parameters that define who owns the activity, the target entity, and the email details. Below is the complete implementation, followed by a breakdown of each part.
 
 Prompt
 public Guid CreateEmailActivity( Guid userId, int objectType, Guid objectId, string mailFrom, CRMUser crmUser, string subject, string body) { try { // Set up a proxy to call the WhoAmI operation and get the user context var bizUser = new Microsoft.Crm.Platform.Proxy.BizUser(); var credentials = new NetworkCredential(sysUserId, sysPassword, sysDomain); bizUser.Url = crmDir + "BizUser.srf"; bizUser.Credentials = credentials; var userAuth = bizUser.WhoAmI(); // Prepare the email proxy var email = new Microsoft.Crm.Platform.Proxy.CRMEmail { Credentials = credentials, Url = crmDir + "CRMEmail.srf" }; // Build the XML payload for the email activity string strActivityXml = <emailactivity> <messagesubject><![CDATA[" + subject + "]]></messagesubject> <messagebody><![CDATA[" + body.Replace(" ", " ") + "]]></messagebody> <ownerid type="" + Microsoft.Crm.Platform.Types.ObjectType.otSystemUser.ToString() + ""> " + userId.ToString("B") + @" </ownerid> </emailactivity>"; // Build the XML for the activity parties string strPartiesXml = <activityparties> <activityparty> <addressused>" + crmUser.GetEmailAddress() + @</addressused> <partyobjecttypecode>" + Microsoft.Crm.Platform.Types.ObjectType.otSystemUser.ToString() + @</partyobjecttypecode> <partyid>" + crmUser.GetId().ToString("B") + @</partyid> <participationtypemask>" + Microsoft.Crm.Platform.Types.ACTIVITY_PARTY_TYPE.ACTIVITY_PARTY_TO_RECIPIENT.ToString() + @</participationtypemask> </activityparty> <addressused>" + mailFrom + @</addressused> <partyobjecttypecode>" + GetObjectTypeCode(objectType) + @</partyobjecttypecode> <partyid>" + objectId.ToString("B") + @</partyid> <participationtypemask>" + Microsoft.Crm.Platform.Types.ACTIVITY_PARTY_TYPE.ACTIVITY_PARTY_SENDER.ToString() + @</participationtypemask> </activityparties>"; // Create the email activity Guid emailId = new Guid(email.Create(userAuth, strActivityXml, strPartiesXml)); return emailId; } catch (SoapException e) { log.Debug("ErrorMessage: " + e.Message + " " + e.Detail.OuterXml + " Source: " + e.Source); } catch (Exception e) { log.Debug(e.Message + " " + e.StackTrace); } return Guid.Empty; } private string GetObjectTypeCode(int objectType) { switch (objectType) { case (int)Microsoft.Crm.Platform.Types.ObjectType.otAccount: return Microsoft.Crm.Platform.Types.ObjectType.otAccount.ToString(); case (int)Microsoft.Crm.Platform.Types.ObjectType.otContact: return Microsoft.Crm.Platform.Types.ObjectType.otContact.ToString(); case (int)Microsoft.Crm.Platform.Types.ObjectType.otLead: return Microsoft.Crm.Platform.Types.ObjectType.otLead.ToString(); default: throw new ArgumentException("Unsupported object type"); } }
 
 That method does the heavy lifting of creating an email activity in CRM. The XML strings are carefully crafted to include the subject, body, owner, and parties. The body replaces line breaks with HTML   tags so that the email content renders correctly in CRM. The GetObjectTypeCode helper translates an integer code into the string representation that the SDK expects.
 
 Once the activity is created, it is in the “Active” state. To make it “Closed” you would normally call the Close method on the activity proxy. However, that method is missing in SDK 1.2, and calling it would raise an error. The workaround is to update the relevant status fields directly in the database. The UpdateActivityCodes function below shows how to do that safely.
 
 Prompt
 public void UpdateActivityCodes(Guid emailId) { try { using (var command = conn.CreateCommand()) { command.CommandText = @" UPDATE ActivityBase SET DirectionCode = @Direction, StateCode = @State, PriorityCode = @Priority WHERE ActivityId = @ActivityId"; command.Prepare(); command.Parameters.Add(new OleDbParameter("@Direction", Microsoft.Crm.Platform.Types.EVENT_DIRECTION.ED_INCOMING)); command.Parameters.Add(new OleDbParameter("@State", Microsoft.Crm.Platform.Types.ACTIVITY_STATE.ACTS_CLOSED)); command.Parameters.Add(new OleDbParameter("@Priority", Microsoft.Crm.Platform.Types.PRIORITY_CODE.PC_MEDIUM)); command.Parameters.Add(new OleDbParameter("@ActivityId", emailId)); log.Debug($"Updating activity {emailId} to closed state."); command.ExecuteNonQuery(); } } { } }
 
 The update touches three columns:
 - DirectionCode – indicates whether the activity was inbound or outbound. For incoming mail you use ED_INCOMING
 - StateCode – the primary state field. Setting it to ACTS_CLOSED transitions the activity from “Active” to “Closed.”