Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Create a custom table with the data from SQLDICTIONARY

Posted on by 449

Hello,

I need the table SQLDICTIONARY to be exported to the lake but since it is a system table, I understand it cannot be exported. And it is needed on some entities schema.

How can I create a custom table that will get the data from SQLDICTIONARY, since the table id is not the same in every environment?

Thanks

  • AstridMalanka Profile Picture
    AstridMalanka 449 on at
    RE: Create a custom table with the data from SQLDICTIONARY

    also, now that I think about it, I am not sure it will work, even if I manage to create the entity.

    Data Lake exports tables and creates views for the entity schema, so it wont be exporting the table anyway because it is a system table.

  • AstridMalanka Profile Picture
    AstridMalanka 449 on at
    RE: Create a custom table with the data from SQLDICTIONARY

    Hi,

    I did something similar for inventory transaction table, never thought about doing it for a system table, I can try that.

    The reason is that we are moving all our systems to F&O and we are exporting the entities to the lake to merge with our current data, but some entities have views that need that sql table.

    For example I got errors building the following views.

    INVENTITEMPRICEID

    INVENTITEMPRICESIMID

    and the reason:

    Invalid object name 'SQLDICTIONARY'.

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,188 Super User 2024 Season 2 on at
    RE: Create a custom table with the data from SQLDICTIONARY

    Hi Astrid,

    Can you explain the business requirement for having table IDs in a Data Lake? What is the underlying reason? What data is exported with table IDs in a column?

    There is a workaround to get system tables in a data entity. First create an entity using the wizard with the naming you need for the system table, but use e.g. the physical table CustTable. When the wizard is completed, you can remove all fields and datasources and build the data entity with any table you need. Be aware that if there is no natural key, you would need to use it for export purposes only. Also rebuild the staging table when you changed the contents of the data entity.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,188 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans