Update NAV Table in SQL from external app - Advisable?

Johannes_Nielsen

Hello guys, quick question for the SQL experts

Using NAV 5.0 on SQL

Our company has merged with another and an application has been developed to extract data from our NAV SQL db to another ERP-system.

The app. uses also modifies custom fields in multiple tables, including Items, Sales Invoice Header and more.

Like these
Enabled Field No. Field Name Data Type
Yes 62101 ERP_ChangeDate Date
Yes 62102 ERP_ChangeTime Integer

Is it advisable to write directly to a NAV Database without the NAV Client?
Could this cause corruption?

-

I guess the fields could just aswell, be placed in another database... :-k

rhpnt

In general it is NOT advisable to change data in NAV tables directly. However, if we're talking about add-on tables (50000+) and fields that do not have any code on any trigger and are not in any form that could have some code then it could be considered possible. :whistle:

ara3n

Please explain what you mean by "Could this cause corruption?"

Johannes_Nielsen

Hi guys, thank you for the replies

ara3n wrote:

Please explain what you mean by "Could this cause corruption?"

What I mean is, could bad programming, inserting a wrong kind of data etc., be a of cause of database corruption / compromise the database integrity.

And maybe even leading to an inoperable system ?

The tables are vital standard tables like 27/Item

DenSter

There are two things that are very dangerous when writing into tables from external apps:
1 - wrong values (NULL values, lower case in Code fields, etc.)
2 - missing validation

Issues with number 1 can actually cause NAV to stop working, issues with number 2 can cause severe inconsistencies in the data.

You have to know 100% what you're doing. If you don't know what you're doing, then don't do it. In your case, I would say don't take the risk.

Johannes_Nielsen

OK, I think I have gotten my suspiciouns about the matter confirmed, either you stick to read-access or you go through the proper APIs.

:thumbsup: Thank you all for your input, this is a real help to us! :thumbsup:

stryk

Well, alternatively you could allow the external application to write into specific "buffer tables" in NAV. Then process these buffer tables with NAV logic (Report, Codeunit, etc.) to update the "real" tables - incl. proper validation etc. ...

davmac1

"buffer" tables work well. I call them staging tables. I use the customers custom tables and depending on the outside app, may even make all the fields text in NAV (varchar in SQL Server).
Nulls will still cause the processing app to bomb, but as long as the processing code is well written and tested, then this method will work.
With NAV 2009, the service tier provides a better way of handling outside data if you have an outside app that can do .net function calls and NAV developer who can write the NAV functions.

ara3n

all nav field have not null property checked. So you cannot have null values in sql.

stryk

ara3n wrote:

all nav field have not null property checked. So you cannot have null values in sql.

True, indeed. What could help here is using the "Maintain Defaults" database property; in this case missing init-values are added automatically!

davmac1

That works well unless somebody on the SQL server side decides to chnge the default settings.

ara3n

That's a different subject. Anybody who is given designer rights to modify table structure could do a lot damage if they do not know what they are doing.

ara3n

Did you get RTC to run from where the NAS is running?
It could be kerebos settings that you need to install.

http://msdn.microsoft.com/en-us/library/dd301254.aspx

And you have to do it for http protocols as well.



http://blogs.msdn.com/b/nav/archive/201 ... am+Blog%29