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

rsaritzky

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

joerg_renz

Hello Ron,

thank you for your reply.
As I understood the property "TableType = ExternalSQL", you don't have to use Tables as "LinkObject = Yes".
I have found this Information - and this works fine:

https://ctrlshiftf4.com/2017/05/22/connect-dynamics-nav-table-with-an-external-sql-database/

Kishorm

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_Guzek

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_renz

just for my understanding:
I have successfully created a REGISTERTABLECONNECTION - How can I access a specific external table with this connection?

rsaritzky

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

joerg_renz

Hello Ron,

thank you for your reply.
As I understood the property "TableType = ExternalSQL", you don't have to use Tables as "LinkObject = Yes".
I have found this Information - and this works fine:

https://ctrlshiftf4.com/2017/05/22/connect-dynamics-nav-table-with-an-external-sql-database/

julkifli33

Hi , anyone
any idea how to make this company abc become dynamic?
can we put it as variable?