CRM in-line search fails with SQL error
After upgrading Microsoft CRM from earlier versions we found that the global search function when enabled failed to return any results, and once the index for the global search had run over a 24-hour period, the in-line search function for any entity would cause a crash and SQL error message to be displayed on page.
In our particular instance this CRM environment had been upgraded from much earlier versions of CRM and included an attempt to solve some upgrade issues by dropping indexes. Initially our thoughts were that the dropping of the indexes were responsible for the problems. However it appears retrospectively that was a fragmentation of indexes that cause the issue. I cannot be exactly sure why the maintenance procedure that is run on the SQL Server did not rebuild and reorganise the indexes sufficiently that the global social function. However the following solution did work for us.
We had pretty much followed the recommendation of this discussion forum.
After submitting a support ticket to Microsoft they requested us to:
- Run following command on CRM database to check fragmentation percentage:
SELECT object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag, page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(”), NULL, NULL, NULL , ‘DETAILED’) WHERE avg_fragmentation_in_percent > 0 ORDER BY ObjectID, IndexID
- In case the fragmentation percent is more than 25-30% we have to rebuild the indexes.
the reference provided by Microsoft was helpful, but not as helpful as we would have liked. We ended up running the following query that automatically rebuilt all the indexes.
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
FETCH NEXT FROM TableCursor INTO @TableName
After doing this, we were then able to turn on the global search and weight the relevant period of time for it to complete the indexing. It appears to have fixed our problem with both global search returning valid results, and in-line search no longer broken when global search was unable.
Changing your password in ZenDesk may affect your Microsoft CRM integration
if you are to upgrade or change the password that you utilise in your ZenDesk system for the account that has been set to synchronise data with the Microsoft CRM platform, you will notice that the synchronisation may not function correctly or may only perform a one-way synchronisation.
You will remember from the instructions that you likely followed in your initial configuration: http://www.interactivewebs.com/blog/index.php/zendesk/zendesk-to-crm-2015-integration/
that part of these configuration settings is to set up your password and username in the SETTINGS / ZD Personal Settings – area of your Microsoft CRM system.
Below is an extract from the vendor’s configuration portal found here
Step 2: Setting up new security roles
The Zendesk integration introduces two new security roles to Microsoft Dynamics CRM that must be assigned before you can proceed to the next step:
- Zendesk – Read configuration settings – grants the user access to Zendesk ticket details in read-only mode To gain access to create/edit Zendesk tickets functionality directly from Microsoft Dynamics CRM, these users must have a valid Zendesk liecense and enter their own personal Zendesk credentials on the ZD Personal Settings page.
- Zendesk administrator – grants access to the global Zendesk Settings page and the Zendesk Entity mappings . Have full access to create/edit Zendesk tickets directly from Microsoft Dynamics CRM.
By default, all users can view Zendesk ticket information in Microsoft Dynamics CRM if the panels are enabled.
To enable the roles, do the following:
- In Microsoft Dynamics CRM, select Settings > System > Administration > Users .
- In the Users page, click New if you need to add new users.
If you are editing a list of existing users, select the user you want to modify and click on the Manage Roles button.
- In the Add Users dialog box, select the role for the group you want to configure.
The two new roles created by the Zendesk integration are at the bottom. Click Next to select and assign the users to a particular role and to send email invitations. Make sure you give yourself the Zendesk administrator role for now so you can complete the setup.
Users are now configured to use the Z endesk for Microsoft Dynamics CRM integration! If you have pre-existing users, you can simply add the appropriate roles to each of your uses.
Note: For users with the Zendesk – Read configuration settings permission, they can individually add their own credentials by navigating to Settings->ZD Personal Settings in Microsoft Dynamics and clicking the New button to add credentials. Enter the Zendesk User ID andPassword then save the record and it will be applied when they access Zendesk tickets. The password will be encrypted so others cannot see the value.