If you are interested, these are the steps we followed to understand the error better, starting from the beginning of creating a new organization without any solutions imported. It appears that CRM is not correctly constructing the SQL statement for full text searches.
- In deployment manager, create a new organization.
- Enable full text search for Quick Find.
- Settings > Administration > System Settings > Enable full-text Search for QuickFind.
- Confirm that the contact is an entity selected for search.
- Check the QuickFind view of the contact entity to ensure last name is included as searchable field.
- Create a contact record with a last name of “Van Horne”
- Normally, we would wait 24 hours before expecting the full text-search to be fully enabled. But because we just installed a new organization as indicated above, we have to wait at least 34 hours from the time of installation. Determining the exact time seems to be possible by running the following SQL and confirming the last run time (UTC) and next run time of the Index Management job for the new organization:
select o.FriendlyName, mj.* FROM [MSCRM_CONFIG].[dbo].[ScaleGroupOrganizationMaintenanceJobs] mj, [MSCRM_CONFIG].[dbo].Organization o WHERE o.Id = mj.OrganizationId AND mj.OperationType = 15
- Confirm that CRMFullTextCatalog at the database level now contains the contact entity and its fields
- Use SQL Server Profiler to trace the event “Stored Procedure > RPC:Completed”
- Use QuickFind on the contact entity to search for “Horne”. This should return the record but instead produces a SQL error.
- Stop SQL Server Profiler trace and search the text column for “QuickFind” and view the SQL. The SQL does not appear to have been constructed properly to do a full-text search. The SQL looks like this (notice the empty brackets on the first line):
exec sp_executesql N'WITH __QuickFind__ as (select top 10001 [ContactId] from () as [__QuickFindInternal__])select
top 51 "contact0".FullName as "fullname"
, "contact0".ParentCustomerId as "parentcustomerid"
, "contact0".Address1_City as "address1_city"
, "contact0".Address1_Telephone1 as "address1_telephone1"
, "contact0".Telephone1 as "telephone1"
, "contact0".EMailAddress1 as "emailaddress1"
, "contact0".ContactId as "contactid"
, "contact0".ProcessId as "processid"
, convert(bigint, "contact0".VersionNumber) as "versionnumber"
, "contact0".ParentCustomerIdName as "parentcustomeridname"
, "contact0".ParentCustomerIdType as "parentcustomeridtype"
, "contact0".ParentCustomerIdYomiName as "parentcustomeridyominame"
, case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]
, convert(bigint, "processidworkflowworkflowid".VersionNumber) as "processidworkflowworkflowid.versionnumber"
from
Contact as "contact0"
left outer join Workflow as "processidworkflowworkflowid" on ("contact0".ProcessId = "processidworkflowworkflowid".WorkflowId)
where
[contact0].[ContactId] in (select [ContactId] from [__QuickFind__]) and (("contact0".StateCode = @StateCode0))
order by
"contact0".FullName asc
, "contact0".ContactId asc',N'@StateCode0 int,@FullName0 nvarchar(200),@FirstName0 nvarchar(200),@LastName0 nvarchar(200),@MiddleName0 nvarchar(200),@EMailAddress10 nvarchar(200),@ParentCustomerIdName0 nvarchar(200),@Telephone10 nvarchar(200),@MobilePhone0 nvarchar(200)',@StateCode0=0,@FullName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@FirstName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@LastName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@MiddleName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@EMailAddress10=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@ParentCustomerIdName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@Telephone10=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))',@MobilePhone0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))'
- On the database, the following query returns one row, proving that, at the database level, full text search appears to work.
exec sp_executesql N'WITH __QuickFind__ as (select top 10001 [ContactId] from (
SELECT "contact0".[Key] AS [ContactId] FROM ContainsTable([ContactBase], (FullName), @FullName0) AS "contact0") as [__QuickFindInternal__])select
top 251 "contact0".FullName as "fullname"
, "contact0".ContactId as "contactid"
, "contact0".ProcessId as "processid"
, convert(bigint, "contact0".VersionNumber) as "versionnumber"
, case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]
, convert(bigint, "processidworkflowworkflowid".VersionNumber) as "processidworkflowworkflowid.versionnumber"
from
Contact as "contact0" WITH (NOLOCK) left outer join Workflow as "processidworkflowworkflowid" WITH (NOLOCK) on ("contact0".ProcessId = "processidworkflowworkflowid".WorkflowId)
where
[contact0].[ContactId] in (select [ContactId] from [__QuickFind__]) and (((("contact0".StateCode = @StateCode0)))) order by
"contact0".FullName asc
, "contact0".ContactId asc',N'@StateCode0 int,@FullName0 nvarchar(200)',@StateCode0=0,
@FullName0=N'((("Horne*" OR FORMSOF(FREETEXT, "Horne"))) OR ("Horne"))'
We probably won't pursue this problem any further as we have recently decided to move from on-premises to online. But perhaps the above information will help you troubleshoot the problem.
Share
Report
All responses (
Answers (
35
