Dynamics CRM Generic SQL Error

Leave a comment

One way a generic SQL error can occur is when dealing with ConditionOperator in QueryExpression.

Consider the following code to get all users who’s first name is Bob:

 QueryExpression userquery = new QueryExpression();
 userquery.EntityName = "systemuser";
 ColumnSet cols = new ColumnSet();
 userquery.ColumnSet = cols;

 ConditionExpression ce = new ConditionExpression();
 ce.AttributeName = "firstname";
 ce.Operator = ConditionOperator.Contains;

 FilterExpression filter1 = new FilterExpression();


 EntityCollection entColRoles = _orgService.RetrieveMultiple(userquery);
 if (entColRoles != null && entColRoles.Entities.Count > 0)
 foreach (Entity entRole in entColRoles.Entities)

You can see we are using ConditionOperator.Contains operator to check the first name field.

If we run this code, we get the error “Generic SQL Error”:

Along with:

If we zoom over the code to set the Contains, you can see we get this message:

“The string contains another string. Value = 49.You must use the Contains operator for those full-text indexing. Otherwise, you will receive a generic SQL error message while retrieving data. In a Microsoft Dynamics CRM installation, only the attributes of the KBArticle (article) entity are enabled for full-text indexing.”

We do not have this restriction with using ConditionOperator.Equal.




Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, BI, Web, Cloud and Data Science.

carldesouza.comLinkedIn Twitter | YouTube


Leave a Reply

Your email address will not be published. Required fields are marked *