Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Best practices in NAV2016/2017 for reading data from an external SQL database
rsaritzky
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.
Thanks,
Ron
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
0
Best Answer
-
Options
joerg_renz
Member Posts: 29
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/5
Answers
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.
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
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
I have successfully created a REGISTERTABLECONNECTION - How can I access a specific external table with this connection?
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
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/
any idea how to make this company abc become dynamic?
can we put it as variable?