Write data into another SQL database/Table using NAV

Alex_Chow

How do you write records into another SQL database or table within NAV?

Is it better to write the records within NAV, share it, then build a SQL view to the NAV table so the other program can access it?

matttrax

In our database we just build views that other databases / programs use. Nothing ever queries a NAV table directly.

Alex_Chow

matttrax wrote:

In our database we just build views that other databases / programs use. Nothing ever queries a NAV table directly.

Do you mean build views from other SQL table that NAV can access?

What if we need other program to view the data that NAV generates?

matttrax

Basically everything we do with cross database functionality and / or other applications accessing NAV data is with a view.

Alex Chow wrote:

Do you mean build views from other SQL table that NAV can access?

Don't have much of this, but when NAV needs access to other data we build a view in the NAV database that is updated from that non-NAV database. I believe Waldo has a blog entry on how to do this...or something close to it. Of course you could also schedule jobs to fill in the data in a real table. I guess it just depends on how real-time it needs to be.

Alex Chow wrote:

What if we need other program to view the data that NAV generates?

We do have a lot of other applications that need data from NAV. For these we build views in the NAV database. Usually these views are used to update other databases for the other applications, but we have some instances where these applications query the view directly.

For the most part we try to avoid querying the tables directly as much as possible. We have a rather large database and user count so we monitor anything that affects performance pretty closely.

Hope that helps a little.

Alex_Chow

matttrax wrote:

We do have a lot of other applications that need data from NAV. For these we build views in the NAV database. Usually these views are used to update other databases for the other applications, but we have some instances where these applications query the view directly.

For the most part we try to avoid querying the tables directly as much as possible. We have a rather large database and user count so we monitor anything that affects performance pretty closely.

Hope that helps a little.

Got it! Thank you! Basically, we create a view from the NAV tables for the other programs to access.

Slawek_Guzek

Reading data is not a problem. NAV table linked to SQL View, linked to external table via linked server or OPENROWSET query (Uff - quite a few links here ).

When I did MySQL integration some time ago, where NAV was supposed to push some data into MySQL based web-shop I used ADO objects at NAV side, connecting to the same database server on which NAV database was installed, plus linked server on SQL side. The main advantage was to maintain security at SQL level.

There are two more alternatives as far as I know. One is direct ADO connection to external database, but this requires writing some library and then translate data to single calls. Not very handy, requires maintaining user/password data in NAV.

Second alternative is to use SQL view linked to NAV table with InsteadOf trigger defined on than view, however I haven't tried this in NAV. Still LinkedServer seems to be the best option when it comes to connect to another server.

Regards,

David_Singleton

In reality it depends on the particular requirement. I would say that most of the cases where I have done this I use linked Views like Mattrax suggests. For sure this is the simplest way, and on SQL 2005 performance is pretty good (2000 started crawling once the number of records went over a few million). I ALWAYS set Datapercompany to NO to make life easier, and never saw an application that needed YES. Make sure to revise your backup procedures (Navision FBK can't handle this) and get a procedure sorted out for moving objects, because of the issue of not being able to import the object if the SQL table does not exist.

If performance becomes an issue, then you would look at ADO. You get tighter control and can basically do exactly what you want.

matttrax

And another note, when you start adding things like views, your database size will of course grow. That is now, however, reflected from the NAV client when you do database information. Our database is 120GB+, but only 90GB of NAV Data. That's using SQL2000 and Navision 3.7, perhaps it is fixed in later versions.

David_Singleton

Oh by the way, when interfacing to SQL tabels directly, one thing NOT to do is to use the Navision C/FLEX granule.

I once designed a system for a client based around a C/FLEX interface to a SQL 6.5 database. Since I had never used C/FLEX, I based the design on the limited on-line help and used that to build the quote for the customer.

Unfortunately when I sent the order to Navision, they said that they had no idea what C/FLEX was, and thus were not able to add the granule to the license. Some how it seems the OnLine help got completed, but they decided to abandon the granule (or at least only sell it to a limited number of partners).

In the end we had to do it all with OCXs. But C/FLEX would have been much simpler.

Slawek_Guzek

matttrax wrote:

And another note, when you start adding things like views, your database size will of course grow.

:shock: of course :shock: :?: :?:
View is definition only, it does not store physically any data unless you put index on it... But I don't remember if indexing views was possible in SQL2000. Or I am missing something ?

Perhaps you're talking about database size as reported by NAV ?

Slawek

David_Singleton

Slawek Guzek wrote:

matttrax wrote:

And another note, when you start adding things like views, your database size will of course grow.

:shock: of course :shock: :?: :?:
View is definition only, it does not store physically any data unless you put index on it... But I don't remember if indexing views was possible in SQL2000. Or I am missing something ?

Perhaps you're talking about database size as reported by NAV ?

Slawek

I don't think Mattrax is talking about indexed views (vSift) we are discussing Navision links to an external database.

matttrax

Yes, I meant that once you start linking NAV with other databases you can no longer 100% trust what NAV says to be the size of your database. Anything that doesn't have an Object ID will not be reflected from the client.

For a DBA that's no problem, but for those who live completely inside of the NAV client, you have to be careful. Not like anything bad will happen, but it's a pain when you request enough space to restore a backup but realize your database is really 30GB bigger that what NAV told you it was...just saying

kriki

Slawek Guzek wrote:

matttrax wrote:

And another note, when you start adding things like views, your database size will of course grow.

:shock: of course :shock: :?: :?:
View is definition only, it does not store physically any data unless you put index on it... But I don't remember if indexing views was possible in SQL2000. Or I am missing something ?

Perhaps you're talking about database size as reported by NAV ?

Slawek

Indexed views are also possible with SQL2000, but it can become very slow because SQL2000 decides too much to rebuild the whole view! In SQL2005 this has been fixed.



I'll give also my 2 cents: My basic idea is that I try to keep the NAV DB clean. Meaning that I never (or try to...) do something outside of NAV in the NAV DB (except SELECTing data).

1)If some application needs to read NAV data, I usually create a new DB with only the views in it pointing to the NAV DB.
2)If some application needs to read NAV data for which a View is not possible, I create a NAV table that I fill up through a NAS. Then the other application can read the data directly or through a view.
3)I also try to avoid that an external program has to write to NAV tables. In this case I prefer to use ADO to read the data in the other DB. If needed I use an extra DB (see nr 1) in which I let it write and then I read the data.

Alex_Chow

Wow... So much great responses from a simple question! Thank you!!