I'm encountering inconsistencies with FetchXML in Dynamics 365; it fails to load all the all attributes each time I run the query. Despite setting up FetchXML to filter and retrieve all attributes, it only returns a subset. Why is this ?

Namrata Zalewski (She/Her) 20

Here is the query that I am using ADF copy activity:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="@{item().tablename}">
    <all-attributes/>
    <filter type="and">
      <condition attribute="modifiedon" operator="ge" value="@{pipeline().parameters.start_date_par}" />
    </filter>
  </entity>
</fetch>

phemanth 11,125 Reputation points Microsoft Vendor

2024-06-26T09:46:36.34+00:00
@Namrata Zalewski (She/Her)

Thanks for reaching out to Microsoft Q&A.

The FetchXML query you’re using is intended to retrieve all attributes for the specified entity within a certain date range. However, there could be a few reasons why it’s not returning all attributes.

Key Columns Missing: When using FetchXML for data migration between two Dynamics 365 environments, if a filter is applied in your FetchXML query, you might encounter an error stating that a key column is missing from the input dataset. This key column is essential for identifying records in the target environment for update or upsert actions. To resolve this error, you should explicitly include the key column in your FetchXML query.

Performance Issues: If you’re selecting many lookup and computed columns and experiencing performance issues, you can try setting the fetch element boolean latematerialize attribute. This setting breaks the query into smaller parts and reassembles the results before returning them to you.

Entity Metadata: You might need to query all the entity attributes separately from the metadata using the RetrieveEntityRequest. Make sure to set the EntityFilter to Attributes. This will retrieve all attributes from the metadata for the said entity type.

Please check these points and modify your FetchXML query accordingly. If the problem persists, please provide more details about the error message or the behavior you’re observing.

Hope this helps. Do let us know if you any further queries.
Namrata Zalewski (She/Her) 20 Reputation points

2024-06-26T21:30:35.8666667+00:00

I am not getting any error message, should I still add the key columns? Also, I am not seeing any performance issues either all data is retrieved but the attributes are inconsistent. When I use table instead of query to copy then all attributes are selected.
phemanth 11,125 Reputation points Microsoft Vendor

2024-06-27T14:31:44.1433333+00:00

@Namrata Zalewski (She/Her) If you’re not getting any error messages and there are no performance issues, then the key columns and latematerialize attribute might not be the issue here.

The inconsistency in attributes could be due to the way FetchXML handles the <all-attributes/> tag. This tag is designed to retrieve all attributes that are valid for read. However, it doesn’t guarantee that all attributes will be returned in every query. Some attributes might not be included if they are not valid for read or if they don’t contain data.

When you use a table instead of a query to copy, all attributes are selected because the table structure includes all attributes by default, regardless of whether they contain data or not.

To ensure consistency, you might want to explicitly specify the attributes you want to retrieve in your FetchXML query instead of using the <all-attributes/> tag. This way, you have more control over which attributes are returned.
phemanth 11,125 Reputation points Microsoft Vendor

2024-06-28T11:49:55.3333333+00:00

@Namrata Zalewski (She/Her) We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 11,125 Reputation points Microsoft Vendor

2024-07-01T04:51:54.8133333+00:00

@Namrata Zalewski (She/Her)was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Namrata Zalewski (She/Her) 20 Reputation points

2024-07-01T21:11:16.7933333+00:00

A possible solution might involve scripting to generate FetchXML with a comprehensive list of attributes. However, determining the necessary attributes for each entity could be challenging given the vast number of entities, each potentially having hundreds of columns. I managed to use the Dynamic CRM API to retrieve the metadata, and we'll explore if there's a method to selectively pull attribute lists based on specific properties. Thank you for your input.
phemanth 11,125 Reputation points Microsoft Vendor

2024-07-03T10:31:49.6333333+00:00

@Namrata Zalewski (She/Her) Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Accepted answer

phemanth 11,125 Reputation points Microsoft Vendor

2024-07-03T10:29:40.09+00:00
@Namrata Zalewski (She/Her) I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer

. Ask I'm encountering inconsistencies with FetchXML in Dynamics 365; it fails to load all the all attributes each time I run the query. Despite setting up FetchXML to filter and retrieve all attributes, it only returns a subset. Why is this ?

Here is the query that I am using ADF copy activity:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="@{item().tablename}"> <all-attributes/> <filter type="and"> <condition attribute="modifiedon" operator="ge" value="@{pipeline().parameters.start_date_par}" /> </filter> </entity> </fetch>

Solution: A possible solution might involve scripting to generate FetchXML with a comprehensive list of attributes. However, determining the necessary attributes for each entity could be challenging given the vast number of entities, each potentially having hundreds of columns. I managed to use the Dynamic CRM API to retrieve the metadata, and we'll explore if there's a method to selectively pull attribute lists based on specific properties.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Was this answer helpful?Yes
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

I'm encountering inconsistencies with FetchXML in Dynamics 365; it fails to load all the all attributes each time I run the query. Despite setting up FetchXML to filter and retrieve all attributes, it only returns a subset. Why is this ?

0 additional answers

Your answer