Helpful CRM links for developers

Here is a list of helpful sites if you are new to customizing Microsoft CRM. There are tons more, but these are the ones that I find myself referencing most often.

The Microsoft CRM Dev center @ MSDN:
http://msdn2.microsoft.com/en-us/dynamics/crm/default.aspx
Tons of great info here, especially in the forums.

Microsoft Dynamics CustomerSource
http://www.microsoft.com/dynamics/customersource.mspx
This is the place for technical support (assuming you have an active subscription).

Microsoft Dynamics CRM Team Blog
http://blogs.msdn.com/crm/
Posts on new features and functionality from the CRM developers themselves. There are also guest bloggers offering up their expertise.

Sonoma Partners
http://www.sonomapartners.com/
These guys literally wrote the bookon CRM customization.

The MscrmGuy’s blog
http://blogs.msdn.com/jstraumann/
This blog has a lot of good info. If you search it for HOL (hands on lab) there are several good tutorials that cover the basics of CRM customization.

SSW’s Rules to better Microsoft CRM
http://www.ssw.com.au/ssw/Standards/Rules/RulestoBetterMicrosoftCRM.aspx
SSW is an Australian consulting service. Their Rules are always helpful, and there are other technologies in the series (such as Reporting Services).

Stunnware
http://www.stunnware.com/
This site has been very helpful. The Javascript library (found in the blog section) is especially useful.

Ronald Lemmen’s blog
http://ronaldlemmen.blogspot.com/
Ronald is a CRM consultant who really knows his stuff. His blog is full of valuable information and he also posts frequently on the Microsoft forums.

Using JavaScript to access the CRM offline database

The stunnware site has lots of great CRM JavaScript examples. One of them shows how to use JavaScript to access the CRM database. The same technique can be used to access the local offline database by changing the connection string to: "Provider=SQLOLEDB;Server=.\\CRM;Database=MSCRM_MSDE;Integrated Security=sspi".

Here is an example:

var accountGuid;

accountGuid = document.getElementById("your fieldname here");

if (IsOnline())

{

// write code for online version here.

}

else

{

var iCount = 0;

var connection = new ActiveXObject("ADODB.Connection");

var connectionString = "Provider=SQLOLEDB;Server=.\\CRM;Database=MSCRM_MSDE;Integrated Security=sspi";

connection.Open(connectionString);

var query = "SELECT count(*) FROM AccountBase WHERE AccountID='" + accountGuid.value + "'";

var rs = new ActiveXObject("ADODB.Recordset");

rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);

rs.moveFirst();

while (!rs.eof) {

iCount = rs.Fields(0).Value;

rs.moveNext();

}

connection.Close();

if (iCount > 0 ) {

//do something useful here

}

else {

//do something else here

}

}

Restoring the CRM database

I’m a developer, not a DBA (even though my employer seems to think I should do both). So when it comes to something outside the realm of writing queries, creating tables, etc., I sometimes have to fumble my way through it. For example, I recently needed to do a point in time restore on our CRM database. I kept getting an error message about not being able to perform the task because the database was in use. This was on our dev version of CRM that I knew no one was using. Finally, I figured out that the IIS service (or just the related app pools) needed to be stopped on the CRM server because it was hitting the database.

Doh!

Remote Desktop console session

Ever try to use Remote Desktop to access a Windows server, and have access denied because all available Terminal Server sessions were in use? Running RDP with the "/console" parameter will allow one more user to connect by using the console session. Just run "mstsc.exe /console" from the command prompt, or set up a shortcut.

Troubleshooting errors when going offline in CRM

Ever seen the error message, "The XML passed to the platform is not well-formed XML", when attempting to take the CRM Outlook client offline?

You can track down the problem by performing a platform trace on the PC that is receiving the error. Microsoft provides info on how to accomplish this here.

After enabling tracing, you can review the log file for the cause of the error. The log can be quite lengthy, so search for words such as “Error” or “Exception”. Often the cause is someone such as myself, has attempted to customize the system in an un-supported manner (one example can be seen here).

Enabling Activities on Custom Entities after they have been created.

This post relates to CRM 3.0 (haven’t been fortunate enough to upgrade yet). There is a great post here by Aaron Elder about how to enable Notes on custom entities. Activities also cannot be enabled after the entity is created, but the post does not explain how to add them.

I was able to take the procedure outlined by Aaron and modify it to enable Activities instead of Notes. Note – this is completely un-supported by Microsoft. The technique has not caused any problems on my test system, but results may vary. Use at your own risk!

The first step is to create a stored procedure using the following script.

Second, paste the name of your custom entity (where is says “placeEntityNameHere”) in the SQL script below, and execute it. This should create all of the needed system relationships between the custom entity and the Activity entities (Email, Task, etc.).

-- Define the Entity to Update

DECLARE @entityname varchar(64);

SET @entityname = 'placeEntityNameHere';

-- Define a GUID for the relationship

-- This is defined here and passed in so that we can access the relationship later

-- and apply special options if needed

DECLARE @TEMP_RelationshipId uniqueidentifier;

-- Add the ActivityPointer Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'ActivityPointer', '_ActivityPointers', @TEMP_RelationshipId;

-- Add the Appointment Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'Appointment', '_Appointments', @TEMP_RelationshipId;

-- Add the Email Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'Email', '_Emails', @TEMP_RelationshipId;

-- Add the Fax Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'Fax', '_Faxes', @TEMP_RelationshipId;

-- Add the Letter Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'Letter', '_Letters', @TEMP_RelationshipId;

-- Add the PhoneCall Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'PhoneCall', '_PhoneCalls', @TEMP_RelationshipId;

-- Add the ServiceAppointment Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'ServiceAppointment', '_ServiceAppointments', @TEMP_RelationshipId;

-- Add the Task Relationship

SET @TEMP_RelationshipId = NEWID();

EXEC dbo.AddCrmRelationship @entityname, 'Task', '_Tasks', @TEMP_RelationshipId;

go

The final step is to add and delete a new custom entity (or use the app provided at Aaron’s post to handle the update). This step causes CRM to re-process all of its relationships, and forces our manual updates to take affect.

Aaron’s example for Notes also required exporting the entity’s customizations and modifying the XML. This is not required for Activities because no changes are made the entity’s form.

Increasing the length of a CRM 3.0 custom entity nvarchar field

Recently, our CRM users asked that the length of a custom field be increased. This seemed like a simple task, however, after a nvarchar field has been created, CRM gives you the ability to decrease the size, but not increase it. To add to the problem, the field was the entity’s primary attribute so it could not be deleted. So, we were left with a few options.
A. Create a new field with the new length, transfer all existing data to the new field, and hide the existing field from users.
B. Back up all data, delete the entity, create a new one, and import all of the archived data.
C. Hack the system by going around CRM’s user interface and modifying the field length.

Option C would give us what we were really looking for (and it sounded like the most fun), so I decided to give it a try.

Here are the steps that were attempted (Warning this is un-supported by Microsoft, and is listed here for educational purposes only! It could seriously harm your CRM installation.):

1. Export the entity’s customizations as an XML file.
2. Open the file in your favorite editor and search for the field’s name.
3. Assuming you only work in one language, there are two values that will need to be modified. They are Length and maxlength. Change maxlength to the new value and Length to two times the new value.

<attribute PhysicalName="Ffs_fieldtomodify">

<Type>nvarchar</Type>

<Length>150</Length>

<ValidForCreateApi>1</ValidForCreateApi>

<ValidForUpdateApi>1</ValidForUpdateApi>

<ValidForReadApi>1</ValidForReadApi>

<IsCustomField>1</IsCustomField>

<DisplayMask>PrimaryNameValidForAdvancedFindValidForFormValidForGrid</DisplayMask>

<Description>The name of the custom entity.</Description>

</attribute>

<field name="ffs_fieldtomodify" requiredlevel="required" maxlength="75" format="text">

<displaynames>

<displayname description="Field To Modify" languagecode="1033" />

</displaynames>

</field>

4. Save the XML, import it into CRM, and publish customizations. (At this point, CRM will show the new length but the SQL tables have not been updated. So, if you try to save a value that is too long you will get a SQL exception message. To solve this, continue on.)

5. Modify the field’s length in the ExtensionBase table of the _MSCRM database. Two views will also need to be updated. The views are and Filtered. You can script the view as “Alter to a New Query Editor window”, and then run the script un-modified. This will not change the definition of the view, but will cause it to be re-compiled and pick up the new field length.

6. To get the field length to display correctly in the CRM metadata browser, update the Attribute table in the _METABASE. You can find the correct row by searching for the field’s name. The length needs to be set to two times the new length (the same value as was entered in the XML file).

select * from attribute where [name] = 'ffs_fieldtomodify'

After all of these steps, everything appeared to be working perfectly. Until, I tried to take my CRM client offline. It gave the error message, “The XML passed to the platform is not well-formed XML”. A CRM client trace revealed this:

>Crm Exception Message: Updating Ffs_temp.ffs_fieldtomodify.Length but it's not valid for update. Original value:150. New value:250., ErrorCode: -2147220991

After thinking about this for awhile, I decided to cancel plan C and go back to plan B. It was an interesting challenge; but in the end it was safer and easier to just delete and re-create the entity. However, if you do not use the CRM offline client, you MIGHT be able modify the field lengths without any problems.