Dynamics Community Forum Thread Details

Implementing Customer Insights - Data caused a schema mismatch

Our production environment only has been updated to the new Customer Insights - Journeys app, which means we are not able to use a calculated field in a segment. This article summarizes how to implement Customer Insights - Data which should allow us to use a calculated field in a segment. We followed the steps exactly.

Unfortunately, the result of this was severely problematic. To skip all of the consequences and jump to the main issue, we started getting the following exception in our code when querying the Dynamics Dataverse Web API, but only in our production environment. We have two other environments (Dev, UAT) which have identical code and solutions, and the error is only occurring in production.

The error is

Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 207

Looking up those errors, I found the following information:

Sql Number 207: In SQL Server, error number 207 typically indicates "Invalid column name". This means the SQL query is referencing a column that does not exist in the table or view being queried.

CRM ErrorCode -2147204784: This is a generic CRM error often associated with database query issues, such as missing columns, incorrect schema, or configuration problems in Dynamics CRM.
Sql ErrorCode -2146232060: This is a SQL Server error code that aligns with issues like invalid object references or schema mismatches.

We get the error when trying to do an expand on a lookup column without specifying any columns to select which will return all columns (the lookup is for a record on the contact table). Here is an example of the request that is causing an error:

/api/data/v9.0/sh_frlocationcontacts?$select=sh_frlocationcontactid,sh_name,sh_contact,_sh_contact_value,sh_frlocation,_sh_frlocation_value,statecode&$expand=sh_frlocation($select=sh_frlocationid,sh_address_latitude,sh_address_longitude,sh_name,statecode,sh_address_line1,sh_address_line2,sh_address_province,sh_address_city,sh_address_postalcode,rb_fragencytraveldistance,sh_frfreezertype,sh_frfridgetype,sh_fsidate,sh_hasvalidfsi,sh_nofsiexplanation,sh_hasrefrigeratedvehicle,sh_doesredistributerescuefood,rb_fragencyfoodprogramtype,_sh_account_value,rb_hastruck,rb_istruckdocklevel,rb_istruckrefrigerated,_sh_locowner_value,sh_locationownerexempt;$expand=sh_LocOwner)&$filter=_sh_contact_value eq '9dac648e-aa51-41b5-93b0-ae1556250e59' and statecode eq 0 and sh_frlocation/statecode eq 0

when we try the same query on our dev dynamics environment (just with different ids due to the different data in different environment), it works.

When we try the exact same query, but add a select statement to the expand, such as $select=contactid, it works in both environments. __It is only when no columns are specified in the expand statement (which will return all columns) that the error is happening in production. T__he following WORKS in prod. I have bolded the only part that was changed from the query above

/api/data/v9.0/sh_frlocationcontacts?$select=sh_frlocationcontactid,sh_name,sh_contact,_sh_contact_value,sh_frlocation,_sh_frlocation_value,statecode&$expand=sh_frlocation($select=sh_frlocationid,sh_address_latitude,sh_address_longitude,sh_name,statecode,sh_address_line1,sh_address_line2,sh_address_province,sh_address_city,sh_address_postalcode,rb_fragencytraveldistance,sh_frfreezertype,sh_frfridgetype,sh_fsidate,sh_hasvalidfsi,sh_nofsiexplanation,sh_hasrefrigeratedvehicle,sh_doesredistributerescuefood,rb_fragencyfoodprogramtype,sh_account_value,rb_hastruck,rb_istruckdocklevel,rb_istruckrefrigerated,sh_locowner_value,sh_locationownerexempt;$expand=sh_LocOwner($select=contactid))__&$filter=_sh_contact_value eq '9dac648e-aa51-41b5-93b0-ae1556250e59' and statecode eq 0 and sh_frlocation/statecode eq 0)

This indicates to us that it is an issue with D365, likely a schema mismatch or metadata issue, as both environments have identical solutions and all column and tables will match between enviornments.

Categories:

Dynamics 365 Customer Insights - Data

Dynamics 365 Customer Insights - Journeys

Microsoft Dynamics CRM

Hello VS-25041511-0,

You've performed excellent troubleshooting by isolating the exact scenario that triggers the error in your production environment. Your conclusion that it strongly suggests a schema mismatch or metadata issue within Dynamics 365, specifically related to how lookups are expanded without a $select statement, is very likely correct.

Here's a deeper dive into why this might be happening and potential avenues for resolution:

Understanding the Root Cause Hypothesis:

The fact that the query works perfectly with a $select statement (even selecting just one field from the expanded entity) points to an issue with how Dynamics 365 constructs or handles the default retrieval of all columns during an expand operation without a $select in your production environment.

This could stem from:

Subtle Schema Differences: While you believe the solutions are identical, there might be very subtle differences in the underlying database schema or metadata between your environments. These differences might not be apparent when examining the solution components themselves but could affect how the Web API processes certain types of queries. This could involve:
- Index Differences: Although unlikely to cause a "Invalid column name" error directly, different indexing strategies could expose underlying schema inconsistencies during complex queries.
- Internal Metadata Inconsistencies: The platform's internal metadata that describes the relationships and columns might be slightly out of sync in production.
- Deferred Schema Changes: It's a long shot, but perhaps a schema change related to the sh_LocOwner lookup on the sh_frlocation entity was partially applied or is in a different state in production.

Optimization Differences: The query execution engine in your production environment might be employing different optimization strategies compared to your development environment. These optimizations could be exposing an underlying issue with how related entity data is being fetched without an explicit column list.

Service Fabric Node Differences: In the backend infrastructure, your production environment might be running on different Service Fabric nodes with slightly different configurations or versions of certain components.

A Bug Specific to the Production Environment's Underlying Infrastructure: It's also possible that this is a transient or persistent bug specific to the underlying infrastructure that hosts your production environment.

Troubleshooting and Resolution Strategies:

Given that your code and solutions are identical, and the issue is isolated to the absence of $select in the expand, here's a structured approach to try and resolve this:

Deep Metadata Comparison:
- Programmatic Comparison: Instead of relying solely on visual inspection of solutions, try to programmatically compare the metadata of the involved entities (sh_frlocationcontacts, sh_frlocation, Contact) and their relationships in both production and your development environment. You can use the Dynamics 365 SDK (e.g., C# code using MetadataServiceClient) to retrieve and compare the schema definitions, including attributes, relationships, and lookup field configurations. This can reveal even the most subtle differences.
- Solution Packager (Advanced): If you exported your solutions, you could unpack them and compare the XML files that define the entity and relationship metadata. Look for any discrepancies in the definitions of the lookup fields and the attributes of the related entities.

Examine System Jobs and Background Processes in Production:
- Check for any failed or pending system jobs in your production environment that might be related to metadata updates or schema changes.

Test with Minimal $select Statements:
- Experiment with adding $select statements that include different subsets of columns from the expanded sh_LocOwner entity in your production environment. This might help pinpoint if the issue is related to a specific column or a group of columns.

Check for Recent Platform Updates in Production:
- Review the Microsoft Dynamics 365 Release Notes and the Message Center in the Power Platform Admin Center for any recent updates that were applied to your production environment around the time this issue started occurring. Look for any known issues or changes related to Web API queries or expand operations.

Simplify the Query:
- Try simplifying the failing query in production. For example, remove the $filter conditions on the expanded entity (sh_frlocation/statecode eq 0) to see if the issue is related to filtering on the expanded data without a $select.

Contact Microsoft Support (Recommended):
- Given the specific nature of the error and the fact that it's isolated to your production environment, raising a high-priority support ticket with Microsoft is strongly recommended. Provide them with the detailed information you've gathered, including:
  - The exact failing Web API query.
  - The fact that it works with a $select statement in the expand.
  - That the same solution works in your development environment.
  - The error codes you've researched.
  - The steps you've already taken to troubleshoot.
Microsoft's support team has access to backend logs and diagnostic tools that can help identify environment-specific issues or potential platform bugs. They might be aware of similar issues or have specific troubleshooting steps for this scenario.

Why the Absence of $select Might Be Triggering the Issue:

When you don't specify a $select during an $expand, the Web API is supposed to return all attributes of the related entity. The error suggests that in your production environment, the process of retrieving all those attributes without an explicit list is failing for the sh_LocOwner lookup on the sh_frlocation entity. This could be due to an unexpected null value in a metadata definition, an issue with how related data is joined internally, or a problem with the serialization of the full related entity data.

By systematically working through the troubleshooting steps and especially by engaging Microsoft Support, you should be able to identify and resolve the schema mismatch or metadata issue that's causing this error in your production Dynamics 365 environment.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak

Quick Links