Best practices in NAV2016/2017 for reading data from an external SQL database

rsaritzkyrsaritzky Member Posts: 469
Hi all,

We have to read (and potentially write) data from an external SQL database. The volume is not super-high (per day, new/modified record counts measured in the 100's at most, and about 6 tables). This data would be "staged" into temporary tables in NAV, then processed via a Codeunit called from a Job Queue Entry to create the "real" tables in NAV.

In pre-2016 days, I would be tempted to write ADO code, so one option is to write ADO.NET code.

I have also been reading about the new linked object "TableType" of "ExternalSQL" and the related commands to connect (e.g. REGISTERTABLECONNECTION and associated commands).

There are a few posts here on MIBUSO that talk about creating views directly in SQL, then using the "old style" linked objects. But there are a number of opinions in the NAV community that I respect that suggest NOT using views or custom stored procedures in a NAV database.

Are there any other techniques that would be considered "Best Practices" in the NAV2016/2017 era? Links to real examples would be appreciated.



Best Answer


  • KishormKishorm Member Posts: 921
    rsaritzky wrote: »
    I have also been reading about the new linked object "TableType" of "ExternalSQL" and the related commands to connect (e.g. REGISTERTABLECONNECTION and associated commands).

    This option is the best one in my opinion as changes are done in the same transaction so an error would rollback any changes made in the SQL tables as well has changes made to NAV tables.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    I have used the new ExternalSQL table type, but finally did not release it into production. I was trying to link NAV to one of our data warehouse tables, and use it as a base for a page, and later for a report available in NAV, but it did not very well.

    I've finally dropped the concept because tests showed that the solution was pretty slow. But, and this is a large BUT, my source table had 80m+ records, so that's likely the reason


    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • joerg_renzjoerg_renz Member Posts: 29
    just for my understanding:
    I have successfully created a REGISTERTABLECONNECTION - How can I access a specific external table with this connection?
  • rsaritzkyrsaritzky Member Posts: 469
    I've been successful in connecting to the external SQL database. I'm not sure what you mean by "Access a specific External Table", but if I understand it correctly, I think it is in the properties of the table you define.

    First build a table in NAV just like normal. However, in the properties of the table, you need to change the external properties.
    This is where you identify the specific table you wish to access:

    ExternalName=Donor; <---This is the name of the table

    Also, you have to define the field names in each of the fields in the "ExternalName" property. For example, if your field name in NAV is "No.", but the field name in your external table is "customer_number", put the value "customer_number" in the "ExternalName" property on the field. You need to do this for all fields.

    As far as I know, you can't "run" the table in NAV to view the table. What I did was define a list page with all the fields, and added this code in the OnInit trigger on the page:




    This has worked for me fine. The external database tables are relatively small (largest one is about 10000 records.)

    Good luck!


  • julkifli33julkifli33 Member Posts: 1,071
    Hi , anyone
    any idea how to make this company abc become dynamic?
    can we put it as variable?
Sign In or Register to comment.