Update Certain Navision tables from SQL directly

NavStudent

I've seen many topics on updating SQL tables directly from sql from other sources. It is always suggested to create staging tables, which make sense creating orders, posting journals. Etc.
It would take way too much time to replicate the business logic, and you can have inconsistent GL transactions.
There is also this rumor that goes around that Navision does not like other system writing data into SQL, and that the data can get corrupted.
First SQL is a db that stores the data. It’s a data repository. Navision writes stored procedures to update SQL. The same statements can be written from other system.
I’ve also heard that the timestamp also doesn’t get updated. This is also false. SQL creates the timestamp and increments during updates.
The only place where Navision complains is Code fields, and how it uses the datetime for both date and time. As long has Code fields are inserted by 3rd party system in uppercase, Navision doesn’t know the difference.

I’ve done many integrations, where master tables were created and updated by 3rd party system, and transactions were inserted in staging table that were posted correctly. They are running currently for many years without any issue.

As long as the business logic is replicated and explained to the 3rd party, it is safe to write to Navision tables.

So I would like to get a proof why this isn’t the case and get rid of this
FUD.

kine

1) Yes, todays it is "safe", but as you know, application logic is the problem
2) For me It is question of responsibility - I am responsible for data in NAV (MS Partner) and I must be sure, that everything is done correctli in the DB and that data are correct for the application logic. But the 3rd party writing into NAV is not responsible for that and are responsible for another application and their data. And because it is hard to check, that the external application will write data correctly in all cases, I will allow them to put the data into "safe" tables and do the rest myself, to be sure that everything is OK and to be able to check, if the problem is in NAV or 3rd party application. It is why I am using this "safe" method for writing data into NAV and I do not like direct writing into some application tables in NAV.

hawwa

I agree with Kine to create a "Safe" table for 3rd party application to write into instead of allow the 3rd party application to write to navision tables directly. This way, I can be very sure all data entered is validated.

Belias

I agree too, BUT I can report a problem (easy to solve, but it's better to not encounter it at all)...
it happened to me with nav 4.03 and sql 2005:
the 3rd party writes in NAV "safe" tables. Those tables may also have "CODE" fields. The problem is that nav recognise code values only when uppercase, and if the partner writes lowercase values in sql, when you open the table you may encounter problems, because nav has not "uppercased" the values!
Transform all "CODE" fields in safe tables in "TEXT" fields and nav will convert them automatically when you transfer data from safe tables to true tables!

kriki

[Topic moved from Navision forum to SQL General forum]