Be careful when using SQL reserved words in your D365FO data entity field names

I though I would share my last troubleshooting that almost caused me brain damage.

A customer complained about a new custom data entity that was working fine when exported to a plain CSV file, but when directing it to a BYOD database it failed to complete.

The error message was: “An Error occurred in add data flow component for Entity Store” which of course does not give us much to go for.

The first thing I did was to remove all but one field mapping in the Data management export project which then completed without errors. Then I kept adding fields until it failed and by doing so, I discovered which field was causing the issue. The field name was “IsAutomaticalSelect”, which is a valid D365FO field name, but whenever that field was included, the export failed.

I then created a local BYOD database on my developer box and ran the export there and sure enough I got the same issue. But now I had full access to the event logs without having to use LCS monitoring, and started traversing through the logs in these three DIXF event folders:

Looking through the error events in the logs can be daunting, but  I found one called “AddEntityDBDataFlowComponent”, which turned out the be the one to inspect in this case.

The following is a similar example using CustCustomerGroupEntity where I added the field NameSelect as a duplicate of the field Description.

At the bottom we find the SELECT statement of the package and by closely examining the code, I found that the statement was not valid. 

In the first yellow marked part in the extracted statement below, the field NAMESELECT is not followed by a comma, which means that the N’Test-BYOD’ is considered part of the field definition.

The second yellow part on the other hand has one comma too many between SYNCSTARTDATETIME and ISNULL.

SELECT N'Test-BYOD' AS DEFINITIONGROUP

            ,N'Test-BYOD-2022-12-15T17:36:25-EAF0BF4CAF964D8BB3EA6AD99BF1C159' AS EXECUTIONID

            ,0 AS ISSELECTED

            ,0 AS TRANSFERSTATUS

            ,0 AS ISPUBLICSECTOR_IT

            ,N'initial' AS PARTITION

            ,N'XXXX' AS DATAAREAID

            ,GETUTCDATE() AS SYNCSTARTDATETIME

            ,ISNULL(T1.CLEARINGPERIODPAYMENTTERMNAME, '') AS CLEARINGPERIODPAYMENTTERMNAME

            ,ISNULL(T1.CUSTOMERACCOUNTNUMBERSEQUENCE, '') AS CUSTOMERACCOUNTNUMBERSEQUENCE

            ,ISNULL(T1.CUSTOMERGROUPID, '') AS CUSTOMERGROUPID

            ,ISNULL(T1.DEFAULTDIMENSIONDISPLAYVALUE, '') AS DEFAULTDIMENSIONDISPLAYVALUE

            ,ISNULL(T1.DESCRIPTION, '') AS DESCRIPTION

            ,ISNULL(T1.ISSALESTAXINCLUDEDINPRICE, 0) AS ISSALESTAXINCLUDEDINPRICE

            ,ISNULL(T1.NAMESELECT, '') AS NAMESELECT N'Test-BYOD' AS DEFINITIONGROUP

            ,N'Test-BYOD-2022-12-15T17:36:25-EAF0BF4CAF964D8BB3EA6AD99BF1C159' AS EXECUTIONID

            ,0 AS ISSELECTED

            ,0 AS TRANSFERSTATUS

            ,0 AS ISPUBLICSECTOR_IT

            ,N'initial' AS PARTITION

            ,N'XXXX' AS DATAAREAID

            ,GETUTCDATE() AS SYNCSTARTDATETIME

            , ,ISNULL(T1.PAYMENTTERMID, '') AS PAYMENTTERMID

            ,ISNULL(T1.TAXGROUPID, '') AS TAXGROUPID

            ,ISNULL(T1.WRITEOFFREASON, '') AS WRITEOFFREASON

FROM CUSTCUSTOMERGROUPENTITY T1

After dunking my head against the wall for a while, I realized that SELECT is a SQL reserved word and it hit me that there might be a connection. Sure enough as soon as I renamed the field to "NAMESELECTION" it worked fine.

 

I did tried naming the field “NAMEWHILE” and that did not fail, so it might just be the SELECT postfix that is producing this error.