Don't use SQL to Update/Insert/Delete a Nav Database?

megawavez · 2010-11-18

Hi,
I've been warned against using SQL to populate a navision database - is this the case or do I just have to be careful. Here are some of the things I've done:
- Set "Maintain defaults" to true
- Made sure transfers into Code fields are all CAPS
- Been careful about what associated tables/fields would be affected by triggers

I'm doing a DB upgrade and using SQL makes certain of data massaging/conversion much easier. We are going from 2.6 to 5.0 SP1.

Are there any other issues that I need to be concerned about that people are aware?

Thanks,
Mega

Savatage · 2010-11-18

megawavez wrote:

Been careful about what associated tables/fields would be affected by triggers

Being careful and being sure can be two different things.
That's why you see the posts about filling journals and having Nav post them correctly.

megawavez · 2010-11-18

Savatage wrote:

megawavez wrote:

Been careful about what associated tables/fields would be affected by triggers

Being careful and being sure can be two different things.
That's why you see the posts about filling journals and having Nav post them correctly.

Yes... although I'm hoping to get a little more information about what breaks. I certainly agree about using journals for such things as posting historical data, but should I worry with such things as Item records, Vendor records, that kind of stuff.

Thanks.

Savatage · 2010-11-18

Ahhh, but you didn't mention just the main tables.

I'm on an older version so dataports & xml ports is our main path to importing & exporting.
As for tables like the item table - you have to cover yourself for all the other related tables like
Product group, Gen Prod Posting Group, Item Unit Of Measure,Inventory Posting group, etc.

What's good about the data&xml ports is you can add Validation. Bypassing validation of fields would be my main concern if if was me. At the end of the day it's your system and you have to be responsible for it.

are you going from sql to sql or native to sql?

megawavez · 2010-11-18

From 2.6 -> 5.0 on SQL2000 (we need to upgrade to SQL2008).

I think some of my other concerns might be weird corruptions of indexes, sift keys that kind of stuff.

Let's say I import an item record and some field on the item record is not exactly as it's supposed to be because of trigger code, I feel pretty comfortable figuring that out, it's underlying not-seen stuff I'm a little more concerned about.

Thanks,
Mega

David_Singleton · 2010-11-18

You are close to the right track, but basically doing it all wrong.

Since version 3.60 the majority of the Navision implementations I have done, I have converted the data from the old system into Navision using SQL. Depending on the clients scenario, I do it one of two ways.

1/ Linked Tables.
If possible, then I create linked tables in Navision that link directly to the source tables of the database being converted. If its converting from a SQL db, or something that can easily move data direct to SQL, then this is a great way of doing it.

2/ New tables in Navision.
In this case I create new tables in Navision, and using some ADO or ODBC or something else, the external program populates these new tables.

In reality there is very little difference between both the above approaches. Though 1/ resolves the issues with timestamps etc.

Now once I have the data in new tables, I write batch routines that take that data and do what is necessary. If it is static data like Customers and Items, its generally just a routine that creates a new Item record in Navision and populates it. If its transaction data, then it depends how much data is being transferred, how it will be summarized etc. But some how that data is used to populate a journal that is then posted.

There are variations on this theme of course. BUT...

I would NEVER recommend the way you are doing of directly populating the actual Navision tables. There is absolutely no saving in time or effort, in fact its generally a LOT more work and testing, and almost always way more expensive.

Sooooo KISS create staging tables of some kind, populate them and create batches in Navision to populate the actual Navision tables.

megawavez · 2010-11-18

Ah... actually, SQL is pretty easy, but probably better to have navision do the actual insertion/deletion of records - haven't look into linked tables, so I'll check it out.

Thanks,
Mega

bbrown · 2010-11-19

If you are spending the effort, why would you not move to the lasted version (2009)?

megawavez · 2010-11-19

bbrown wrote:

If you are spending the effort, why would you not move to the lasted version (2009)?

I'm about 80% through the upgrade to 5.0 and it's gone on for a lot longer than I'd like, so plan is to go to 5.0, then plan on 2009 at a later date.

megawavez · 2010-11-19

So I have a specific example:
Our current database uses the "Job No." throughout the database (sales lines, purchase lines, sales invoice lines, etc). As part of the upgrade, I'm going to create a dimension "JOB" for all records in the database where "Job No." is used (document dimension, posted document dimension, etc.), then empty out the "Job No." field. It's actually a bit easier to perform this operation using SQL (and faster) and seems on the surface to be a safe operation (I don't see any flow fields that reference these record). Is this a dangerous operation? Is there something that Navision would do when inserting these records that a straight SQL insert doesn't (besides nav trigger code).

Of course the safe route is to write nav trigger code that does this.

Thanks,
Mega

Howdy, Stranger!

Quick Links

Categories

Don't use SQL to Update/Insert/Delete a Nav Database?

Comments