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.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
