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();
 cols.AddColumn("systemuserid");
 userquery.ColumnSet = cols;

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

 FilterExpression filter1 = new FilterExpression();
 filter1.Conditions.Add(ce);

 userquery.Criteria.AddFilter(filter1);

 EntityCollection entColRoles = _orgService.RetrieveMultiple(userquery);
 if (entColRoles != null && entColRoles.Entities.Count > 0)
 {
 foreach (Entity entRole in entColRoles.Entities)
 {
 Console.WriteLine(entRole.Attributes["systemuserid"].ToString());
 }
 }

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.

 

 

ABOUT CARL DE SOUZA

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 *