ArcherPoint Weekly Microsoft Dynamics NAV Developer Digest – vol 18
The ArcherPoint technical staff—made up of developers, project managers, and consultants – is constantly communicating internally, with the goal of sharing helpful information with one another.
As they run into issues and questions, find the answers, and make new discoveries, they post them companywide on Yammer for everyone’s benefit. We in Marketing watch these interactions and never cease to be amazed by the creativity, dedication, and brainpower we’re so fortunate to have in this group—so we thought, wouldn’t it be great to share them with the rest of the Microsoft Dynamics NAV Community? So, the ArcherPoint Microsoft Dynamics NAV Developer Digest was born. Each week, we present a collection of thoughts and findings from the ArcherPoint staff. We hope these insights will benefit you, too.
Question: I am working on an upgrade where there have been a few issues with SQL Server versions and collations that appear to be resolved. Now we are seeing some issues around creating an ADO connection. It appears to me there may be a network security setting that is causing the user to answer prompts to "allow access from this site" every time the code tries to launch the SQL Server stored procedure.
There are no file related commands. The stored procedures are reading views from one server/database and inserting rows into a table on a second server/database. All stored procs run fine from SSMS so we know they are good. I think it has to be something related to permissions or network security. All linked servers are accessible from SSMS.
We have fixed the issue around the connection timeout error. We are now seeing an issue with using the ADO connection and running the stored procedure. I have spotted some blogs stating that you cannot use ‘Microsoft ActiveX Data Objects 2.8 Library’ in RTC. I am going to try the new DotNet datatype to see what happens. If anyone has used this method in 2013 R2 shoot me a message. Thanks.
Nitin Patil replied:
Do any of these stored procedures have flat file copy or creation code?
If yes, then try changing the folder path to something easily accessible (i.e. NAV server folder) instead of remote or SAN based folders.
I'm using ADO in Dynamics NAV 2013 and haven't tried R2 yet. They must run on the client side obviously. As far the DotNet data type, NAV's own data migration toll uses this approach.
Question: I have a requirement to call a SQL procedure from Navision, capture the dataset, and store that into a Navision table. Has anyone done something similar? Is it possible to do? This is for Navision version 4.00.
Yes, this is possible. In 4.0, you'll be limited to calling ADO as an Automation or OCX variable. And you won't be able to directly store the results into a NAV table, but you have two options there.
First, you can do it the hard way. Loop through the resulting SQL dataset record by record, and copy that into your NAV table field by field.
Second, you can have the SQL procedure store the data into the NAV table directly - however, this will bypass any and all NAV table triggers.