Updating / Inserting data directly into SQL-Navision-Databas

Rihi

Hi there,

we are running Navision 3.70 (5.0SP1) <-- that's how it is being presented in the Navision client itself.. from my understanding, it is technically 3.70 but running on a 5.0 client, not 100% sure though) on a SQL 2008 database server.

In the first step of a project, we would like to read some data and update it. We have a strongly customized version for our service area.

So if we use for example the [xx]$[Service Header] with a specific date, we just want to change that date via a PHP script or something like that.

There is no codeunit or anything like that behind the datafields, we'd like to change.


In the second step of the project, we would also like to insert new data into the database, like adding new lines into the [xx]$[Service Item Line].


My question would be if anyone has ever done that before (I guess so ;-), what the disadvantages are and what we should keep in mind when doing that (especially for the 2nd part), and also if the first part might let us run into problems.


Any info / feedback would be highly appreciated!

Thanks in advance

davmac1

You could upgrade to NAV 2009 SP1 and use the application tier to do your updates using web services.
You can even buy a nice supplemental book that gives some good examples on how to do it.
You would not be able to run the role tailored client without an object upgrade, but the executable upgrade should work for you using the Classic client.

stryk

( I guess you have 3.70 objects/application on 5.0 SP1 technology/CSIDE - and with SQL 2008 you should have NAV 5.0 SP1 Update 1 or higher)

Well, writing directly into NAV tables is technically possible, of course, but there are some requirements:

1) You MUST use NAV compatible datatypes and values; e.g. when inserting a NAV "Code" type field (= SQL varchar) only uppercase values are allowed; etc.
2) You MUST NOT insert "Null" values; hence, you have to provide 100% of all record-fields. If you don't want to provide all fields, then you could enable/use the "Maintain Defaults" database property.
(More about this: http://msdynamicsbooks.com/product_info.php?products_id=130&language=en&osCsid=c43beba9c2b0f50dffa2e1a65db49539 )
3) Further, you have to be aware that you are bypassing any NAV business logic, hence there is not validation of the data - this could mean you jeopardize the data-integrity.

Finally, writing directly into real NAV ERP/SCM tables is not really recommended. You should rather create some buffer table and insert the data into this. Then you should run some NAV function (NAS, Job Scheduler, etc.) to process the data, doing all necessary validations when inserting into the "real" NAV tables (similar to the WebService suggestion).

Rihi

Hi,

thanks for your replies!

( I guess you have 3.70 objects/application on 5.0 SP1 technology/CSIDE - and with SQL 2008 you should have NAV 5.0 SP1 Update 1 or higher)

That is our situation, as far as I know.


Hmm okay, there are some things we have to consider and keep in mind, but your concerns are more regarding the insertion of new data, I guess?

So the first step, updating (changing) only some of already existing values should not be a problem?


And for the second step - inserting new data - we also thought about using a transfer-table and using a NAS to do the logicstuff, but not sure yet how to exactly do that... do you know how I could get more information on how to do that? Is there some good literature or anything else?

Thanks again!


edit: Oh and by the way, we bought your 'NAV/SQL Performance guide' already, nice book with interesting tipps. Wasn't aware though, that there is also information regarding the insertion stuff in there, have to read it again, I think


edit2: Regarding the NAV2009 update - do you really think this would work? We have most of the stuff customized in Navision and our external consultants said it would be quite a massive , thus expensive, project to upgrade to Nav2009 (well probably because of the Object upgrades)... so would it be like
- Install Nav2009 client
- connect to (test-) db
and that's it? Or do I have to set some special option so that the objects won't be recompiled?
I am more the sql admin and not really familiar with Navision itself... ;-)

stryk

Rihi wrote:

Hmm okay, there are some things we have to consider and keep in mind, but your concerns are more regarding the insertion of new data, I guess?
So the first step, updating (changing) only some of already existing values should not be a problem?

Still you have to take care about valid/compatible values regarding SQL and NAV datatypes!

Rihi wrote:

And for the second step - inserting new data - we also thought about using a transfer-table and using a NAS to do the logicstuff, but not sure yet how to exactly do that... do you know how I could get more information on how to do that? Is there some good literature or anything else?

Well, this is not a "standard scenario". ACtually it depends on what exactly you want to do: e.g. creating "Sales Orders" rund different business logic than creating "Contacts".
Thus, once you have saved the (valid) raw data into your "transfer table" you need some NAV developer (or someone who knows standard NAV and your customizations) to process the business logic properly.

Rihi wrote:

edit2: Regarding the NAV2009 update - do you really think this would work? We have most of the stuff customized in Navision and our external consultants said it would be quite a massive , thus expensive, project to upgrade to Nav2009 (well probably because of the Object upgrades)... so would it be like
- Install Nav2009 client
- connect to (test-) db
and that's it? Or do I have to set some special option so that the objects won't be recompiled?
I am more the sql admin and not really familiar with Navision itself... ;-)

Basically there are 2 types of upgrades:
1) Complete Upgrade: you change the NAV technology (CSIDE) and the application (objects)
2) Technical Upgrade: you change the NAV technology (CSIDE) but using the old the application (objects)

Indeed, an upgrade type 1) could be quite an issue, taking a lot of effort. Update 2) is - more or less - a piece of cake; as you say: you just open the db with the new client and convert the db.
But of course you have to take care about proper setup & configuration, using the right versions etc..

With a TU to NAV 2009 you could still run the old business logic in "Classic Client", but you could use the "Service Tier" for the "Webservice" feature!

Rihi

Hi there,

thanks again for your answers.

I think in the first step we are trying to do some updates and some easy inserts directly into the SQL server, and for adding lines to our main table, I guess we are first waiting and trying to make sure that nothing bad will happen ;-)

The update itself is something we are planning already, but we estimate it will be more or less like a 1y project (incl. objects), but maybe we are going to try it on a testserver first.

Well anyway, thanks again for your help, really appreciated!