27 July 2008

CRM 4.0 upgrade: There is already an object named 'AttributeTypes' in the database.

This week I was helping a customer to upgrade their CRM 3.0 system to CRM 4.0.
The upgrade pre-check looks fine with all passed, no errors, no warnings.
But during the upgrade process, it first alert an error regarding C360, then I fix the problem, click the "Retry" button, it then generates another error:

" Microsoft.Crm.Setup.Server.InstallDatabaseAction failed.
System.Data.SqlClient.SqlException: There is already an object named 'AttributeTypes' in the database. "

That's very strange. But finally I found it's a design lack of upgrade process: The 'Retry' doesn't actually do 'Retry'!
So I had to:
1. Stop the upgrade process and uninstall CRM 4.0;
2. Restore CRM 3.0 databases, then install CRM 3.0 with existing databases;
3. Fix the problem which generated the first error;
4. Re-run the CRM 4.0 upgrade.

I did know there are some complains about C360 add-ons stop the CRM upgrade process, these could be the reasons:
1. The C360 uninstall doesn't do a clean uninstall all the time, so you may still see C360 icons on the CRM form, but no function;
2. Some C360 products create tables inside the CRM database which is not a recommended method, and again once you uninstall the product, the tables are still there.
3. C360 has a cleanup tool which does cleanup the remained infomation in the onLoad, onSave, and isv.cofig.xml etc. However, it doesn't cleanup the database. And the tool is hard to find on C360 website.

C360 provides some great add-ons for MSCRM, but we wish it could be better!

20 July 2008

CRM 4.0 E-mail access type bulk setting tool

(Update: You can use CRM 4.0 Workflow to do it as well, it's up to you to choose which one you feel comfortable.)
Microsoft Dynamics CRM 4.0 doesn't provide a tool for CRM User's email access type bulk setting. So I write this application to helping CRM Administrators for this purpose. It takes me 2 hours work so don't expect too much, however it does work. ;-)

1. Launch the application, type in the following information:
CRM Server(the CRM discover server address): e.g http://localhost:5555
Organisation(the Organisation's name, not Friendly name): e.g AdventureWorksCycle
SQL Server: e.g localhost
Database: e.g Adventure_Works_Cycle_MSCRM
CRM User: e.g CRMAdmin
CRM User's Password

2. Click the 'Load' button, it will list all enabled CRM users whom belonging to this organisation with their email settings.

3. Select users your want to modify, then select Incoming E-mail and Outgoing E-mail setting from the picklist. If you don't want to change both of those, then just select blank in the picklist.

4. Click the 'Update' button, it will update user settings you selected.

Download it here: http://code.msdn.microsoft.com/crm

Enjoy it!

Jim Wang

13 July 2008

CRM 4.0 E-Mail Router for hosted Exchange

I have a client who uses a hosted Exchange for their email system (also hosted AD) and my client uses CRM 4.0 On-Premise edition. So the CRM E-Mail Router solution is:
  • Outgoing: local SMTP service, use local system account
  • Incoming: hosted Exchange, use a service account which can access the remote Exchange mailbox
  • Both Outgoing and Incoming are configured to use Email Router

When test the connection, the outgoing STMP works fine, however, the incoming Exchange get the following error:
"Incoming Status: Failure - The remote Microsoft Exchange e-mail server returned the error "(404) Not Found". This user or queue does not have a mailbox. Create a mailbox and try again. The remote server returned an error: (404) Not Found. "

It is strange because I'm sure the user has mailbox! Thanks for Tony Iadarola who is a senior engineer works for the hosting company , after analyzing the Exchange server logs, he pointed out that the CRM E-Mail router service try to query the mailbox by using the 'half' SMTP address!( which is not recommend, MS recommend either SamAccount or Legacy DN, or mailbox GUID is used)

e.g.: In our case, we have a test user in CRM, the user's SamAccount name is: TU341-NBG125, SMTP mailbox: TUser@domain.com which is the user's primary email address in CRM. The CRM E-Mail Router service should be making a request to Exchange mail server for the Test users mailbox using http://ExchangeMailServer/Exchange/TU341-NBG125 instead it is using http://ExchangeMailServer/Exchange/TUser

If I change the user's primary email address to TU341-NBG125@domain.com, the problem will disappear! However the email address doesn't mean anything to customers, so I can't change the email address.

Finally I found a workaround: edit ..\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml
Search the test user section(within the tag: <ProviderConfiguration>), edit it's <EmailAddress> tag, change the email address from TUser@domain.com to TU341-NBG125@domain.com, then save the xml file, modify it's property to Read-Only, restart the CRM E-mail Router Service.(You have to make the file read-only first, otherwise the service will re-write the <EmailAddress> tag by using the user's primary email address)

The problem is solved, all users still use their normal email address, and the CRM E-Mail Router is able to create an Email activity for incoming emails with correct email address. However I think the CRM E-mail Router should be improved by query mailbox via SAM, instead of using SMTP address which may not unique for a hosted environment.

16/07/2008, add to my post: the service account needs to have permission to access 'Root' and 'Inbox' folder of user's mailbox. e.g. if the service account has such permission, it can find user's mailbox by using the 'half' SMTP address (http://ExchangeMailServer/Exchange/TUser ). However it's might be a problem for a hosted company who doesn't want to grand the permission for the service account.

02 July 2008

CRM 4.0: Use JavaScript execute/call/launch CRM Workflow

I have a question from my colleague: How to use JavaScript execute workflow in CRM 4.0? The question also repeats very often in CRM Forums, no answer so far.

In CRM 3.0, Mitch Milam has described how to Launching a Workflow Rule from JavaScript, it works great. However, in CRM 4.0, the class: ExecuteWFProcessRequest has been deprecated, so it won’t work in CRM 4.0. Although there are many ways to launch a workflow, if you want to run it through JavaScript, here’s the trick:

/* the function */
ExecuteWorkflow = function(entityId, workflowId)
var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <Execute xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <Request xsi:type=\"ExecuteWorkflowRequest\">" +
" <EntityId>" + entityId + "</EntityId>" +
" <WorkflowId>" + workflowId + "</WorkflowId>" +
" </Request>" +
" </Execute>" +
" </soap:Body>" +
"</soap:Envelope>" +

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
var resultXml = xmlHttpRequest.responseXML;

/* call */
var theWorkflowId = "3FD2DD58-4708-43D7-A21B-F0F90A0AA9F2"; //change to your workflow Id
ExecuteWorkflow(crmForm.ObjectId, theWorkflowId);

Enjoy coding! :)

01 July 2008

Failure: The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes

Last week I had a chance to help a London company to upgrade CRM 3.0 to 4.0. As you can see there are some warnings and error, I can easily fix all warnings instead of ignore them(in fact, most warnings are safe to ignored). The error is an interesting one, regarding Microsoft KB(927226), it’s a custom indexes error, when I check the installation log, it tells me the table name: DocumentIndex

C:\Documents and Settings\user\Application Data\Microsoft\Mscrm\Logs\crm40svrsetup.log
13:51:00| Info| The following full-text indexes are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes:
13:51:00| Info| tableowner: dbo
13:51:00| Info| tablename: documentindex
13:51:00| Info| fulltextkeyindexname: cndx_primarykey_documentindex
13:51:00| Error| remark: modified full-text index
13:51:00| Error|
13:51:00| Error| The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes.
13:51:00| Error| Check SqlFullTextIndexValidator : Failure: The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes.

I saw another post here which has the same error message. However I found that my case is slightly different, in fact I can’t see any full-text catalog against this table. So I just create a new ftcat_documentindex against DocumentIndex table(after compare with my VPC), then the error gone. :)
The full-text catalog is call: ftcat_documentindex, see below: