MSCRM Dynamics

• Customization file export, import and publish
• System Jobs were running
• I was able to make entity changes and publish
• Created and saved new records
• Able to run workflows

Features not Working

• Reports
• Knowledge Base full text search was not working 

 The was no event viewer error recorded for the Knowledge Base issue, just the friendly, 'an error has occurred, see your system admin....' error when I tried to search the Knowledge Base using full text search.  All other search types worked fine (Subject, Article Number, etc.)

The report error did log an event and it was the one I don't generally like to see: "Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'".  When I see that error, I usually know there are issues related to folders, files, datasource used for the report or paths.

Fortunately, I was able to resolve the KB full search issue by following the instructions in this KB Article.  The KB issue was fairly easy to address by starting the service, adding the user to the Group as explained and rebuilding the catalog.  After I completed those steps, I was able to use the KB full text search.

The Report Server issue was a bit trickier.  I followed the instruction in the KB Article above but that did not correct the issue for me so I did some additional research and found this great article with a significant amount of information for Troubleshooting Dynamics CRM reports.  I previously tried installing the SRSS Data Connector but that didn't fix the issue so I uninstalled it and configured the datasource to use Microsoft SQL Server rather than the connection type of Microsoft CRM Data Extension. 

The next thing I tried was disabling the organization in the Deployment Manager because I noticed that the Report Server URL did not have the port number in the string (not sure how that happened).  I disabled the Organization and edited the string for the report server to include the port number then selected the Next button.  When I finished the wizard, CRM worked through the update process and in the end returned an error:

Event Type: Error
Event Source: MSCRMReporting
Event Category: None
Event ID: 19969
Date:  10/27/2009
Time:  10:46:48 AM
User:  N/A
Computer: ServerName
Description:
Web service request SetItemDataSources to Report Server http://CRMSQLServerName:5555/reportserver/ReportService2005.asmx failed with SoapException. Error: The data source 'MS Sql' cannot be found.

I ignored the error above and enabled the Organization.  As expected, the Reports still did not work, but there was a new Report Folder on the Report Server with the new set of reports created as a result of updating the URL string for the report server.  My hope was that this change corrected any issues related to the previous report folder so I installed the SRSS Connector and did an iisreset and that fixed the issue.

I hope this information proves useful for someone.  Correcting the reporting issue was challenging but I learned a lot through the process and found some really great online articles with a wealth of troubleshooting information.

In troubleshooting an e-mail router issue today, I needed some information about the xml e-mail router settings and when I Binged my question, I found this great article on the CRM Team Blog site that provided exactly what I needed.  If you have a need to better understand the xml files associated with the CRM E-mail Router then  this article should provide what you need.

Email router configuration XML file explained - Microsoft Dynamics CRM Team Blog

The Dynamics CRM database can sometimes grow quite large and one of the culprits could be the AsyncOperationBase table.  The good news is that Microsoft included a fix in Update Rollup 6 for Microsoft Dynamics CRM 4.0 to better manage this issue.  UR 6 will address newly added records so for those whose table size is already large, you need to take a few additional steps to clean up the existing data. 

KB 968520 contains all the information you need to clean up the existing data in your asyncoperation table.  One item to consider before applying this solution is that completed system jobs contain historical information.  You should check with Management to ensure the company is ok with not having data for completed workflows associated with the following:

• SQM data collection. - Software Quality Metrics which collects data for the customer experience program.
• Update Contract States SQL job. This job runs one time per day at midnight. This job sets the expired contracts to a state of Expired.
• Organization Full Text Catalog Index - populates full text index in db for searching kb articles in CRM.

Most companies will not mind but it is always a good practice to check before removing data.  Also, always perform a database backup before taking this type of action on a database.  It's a good practice to have a current backup in the event that something doesn't go as expected.

Read through the KB article carefully as there is some good information about how to optimize performance of the Deletion Service that removes records from the database that were marked for deletion, and a SQL script that you can run to show the count of the records that will marked for deletion. 

One final item for consideration is that you may want to consider running a Shrink Database procedure after the Deletion Service removes the records from the database (DBCC SHRINKDATABASE).  You can learn more about this procedure by following the link above or visit Microsoft Tech Net.  Be sure to read through all the information carefully and take SQL best practices into consideration before performing the operation.

In working through some optimization on a CRM server, I thought it would be a good idea to check the running System Jobs.  This particular CRM installation was upgraded from 1.2 to 3.0 to 4.0.  Given that, I knew there were a few workflows that needed a face lift so before diving into them, I decided to gather information.

My first step in the process was to create an Advanced find view that returned all System Jobs where the Status Reason = Waiting, Waiting for Resources, Pausing, In Progress.  I added the columns Error Code and Message to the result set for additional information.

The results of the query showed that there were several thousand jobs running in various states. Here is some of the information I was able to glean from the System Jobs.

Several of the jobs failed for one of the following reasons:

·          The system was not able to execute a Send E-mail step.

o    The entity record containing the e-mail address information did not have an e-mail address

·          The record used to populate the Regarding field of the e-mail was not a valid record for this use

·          The record the workflow was trying to update was in a 'Read Only' state

·          Timeout periods spanned multiple days

o    Workflow was in a waiting pattern but the record that the job was running against was closed

Armed with the information above, I was able to go through the workflows and re-design with them to behave more intelligently and better handle some of the data anomalies and scenarios that they would encounter.

Those improvements included:

·          Added a Check Condition step that checks to ensure the e-mail address field contains data before all Send E-mail steps.

o    I send a notification to an internal user if the e-mail address field of the record does not contain data.  The notification instructs the user to update the record with the e-mail address and run the workflow again.

·          Added a Cancel Workflow step that follows the Send E-mail notification to the internal user.

o    This ensures that the System Job is canceled when it is not able to complete

·          Ensured all Regarding fields of E-mails generated from the System were updated with valid record types

o    Contacts, Accounts, Leads, Opportunities, Quotes, etc. are all valid record types.

o    This step will fail if you try set the Regarding field to another Activity type like E-mail, Service Activity, etc.

o    If you are not sure if the record you are using to set the Regarding field in an e-mail is valid, you can create a CRM e-mail and click on the Regarding field to ensure the record type you are using appears in the list.

·          Added a Check Condition step that checks the record status to ensure it can be updated before the Update step.

·          Added a Cancel Workflow if the record is in a Read-Only state

·          If the timeout period spanned a few days, I added a timeout step for each day

o    Added a Check Condition step before each timeout day to check the record status

o    Added a Cancel Workflow step to cancel the workflow if the record was closed during the day

My initial goal was to see if I could identify improvements that would reduce the result set of the query by 50%.  Applying the design improvements above, I was able to reduce the result set from several thousand to several hundred and my invested time was about 8 hours of effort. 

The above is intended as an example only and the specific steps referenced may not be the optimal approach for every CRM configuration.