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

rsaritzkyrsaritzky Posts: 428Member
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.

Thanks,

Ron
Ron

Best Answer

Answers

  • KishormKishorm Posts: 869Member
    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 Posts: 962Member
    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

  • joerg_renzjoerg_renz Posts: 15Member
    just for my understanding:
    I have successfully created a REGISTERTABLECONNECTION - How can I access a specific external table with this connection?
  • rsaritzkyrsaritzky Posts: 428Member
    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:

    LinkedObject=Yes;
    LinkedInTransaction=No;
    TableType=ExternalSQL;
    ExternalName=Donor; <---This is the name of the table
    ExternalSchema=dbo;

    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:

    IF HASTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'TestExternalDB') THEN
    UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'TestExternalDB');

    DATABASE.REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'TestExternalDB','Data Source=SERVERNAME\SQLSERVERNAME;Initial Catalog=TestExternalDB');

    SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'TestExternalDB');

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

    Good luck!

    Ron

    Ron
Sign In or Register to comment.