Note: This post propose a solution which will render your farm in an unsupported state, and should only be done under Microsoft Support approved guidance. A bug for the issue has been filed, and I expect a fix will be provided with a CU later on. Another fix is to re-create your Service Application and get a fresh database.
I’m doing a search project where we have a bunch of query rules defined. Some are promoted results and some change what is displayed and the sort order. For some of the rules we use trigger terms from a term store, which works just fine.
The solution is created on a dev farm and then the search configuration from the search site is exported and moved to the production farm. So far so good.
Importing the search configuration in production works just fine, but when you try to access the query rule page (http://intranet/sites/search/_layouts/15/listqueryrules.aspx?level=sitecol) you get the following error:
Checking the ULS we get the description below where I’ve highlighted the error.
Getting Error Message for Exception System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Data is Null. This method or property cannot be called on Null values. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)
at Microsoft.Office.Server.Search.Query.Rules.QueryConditionDatabaseHelper.ReadQueryConditions(Dictionary`2 qrs, IDataReader reader)
at Microsoft.Office.Server.Search.Query.Rules.QueryRuleDatabaseManager.QueryRuleCollectionBuilder(SqlDataReader reader, SearchObjectOwner consumer, Boolean isReadOnly, Dictionary`2 ownersDictionary, Boolean ignoreDisableInheritance)
at Microsoft.Office.Server.Search.Query.Rules.QueryRuleDatabaseManager.GetQueryRules(SearchObjectFilter queryRuleFilter)
at Microsoft.Office.Server.Search.Query.Rules.QueryRuleDatabaseManager.GetQueryRulesBatched(SearchObjectFilter queryRuleFilter, Int32 batchSize, Int32 start, Int32& totalRules)
at Microsoft.Office.Server.Search.Administration.SearchServiceApplicatio...).
Some of the exported query rules are as mentioned tied to term sets. For this project the term stores are not created with a script, thus termset guid’s differ on the farms, and this is what is causing the error.
When I removed the offending rules from my search configuration before import, the listing of the rules worked just fine.
Problem solved? Hell no!
A side effect brought in from the configuration with the termset triggers was that any query rule residing in a query rule group, would not fire at all. If the rule was moved outside a group it worked just fine. ULS also showed that the NULL error still occurred. Ok, let’s remove the query rules then.. but how? No way to see them in the UI, and PowerShell bombed with the same NULL error. Time to put on the DB admin hat.
Examining the database I saw numerous query rules from all my failed efforts as well as orphaned ones from failures on test site collections. But with the DB hat on there is nothing we cannot accomplish!
The below script retrieves query rules by name, in my case the offending rules were named “Block: something people something”. Then it executes the proc_MSS_RemoveQueryRule stored procedure in the Search Service Application DB. Once run, all should work just fine again!
DECLARE @Id uniqueidentifier
DECLARE @OwnerId uniqueidentifier
DECLARE @LastModified datetime
-- Iterate over all matching query rules
WHILE (1 = 1)
BEGIN
-- Get next query rule
SELECT TOP 1 @Id=Id, @OwnerId=OwnerId, @LastModified=LastModified
FROM [dbo].[MSSQueryRules]
-- Filter on your faulting rule
WHERE DisplayName like 'block%people%'
-- Exit loop if no more query rules
IF @@ROWCOUNT = 0 BREAK;
-- call your sproc
exec [dbo].[proc_MSS_RemoveQueryRule] @Id, @OwnerId, @LastModified
END
