SQL and NAV Integration

fazlehasan

I have two question:

(1) Can a table be designed first in SQL and then used in NAV C/SIDE(in the range of 50000...50999)?
We have 2 companies - I want the table designed in SQL available to both of the company
while opening in C/SIDE.

(2) I have to copy a NAV table to another SQL database in the same server - is it possible
using C/SIDE C/AL?

We are using NAV 5.0 SP1 and SQL 2005.

If you have any suggestion please share.

ara3n

why don't you create the table in nav and change the property, percompany to false.


If you can't you can still create the table in sql and then create a view for the table. Then in nav create the table and link it to the view.

stryk

fazlehasan wrote:

(1) Can a table be designed first in SQL and then used in NAV C/SIDE(in the range of 50000...50999)?
We have 2 companies - I want the table designed in SQL available to both of the company
while opening in C/SIDE.

See Rashed's reply.

fazlehasan wrote:

(2) I have to copy a NAV table to another SQL database in the same server - is it possible
using C/SIDE C/AL?

No, with C/AL this is hardly possible. I suggest to look into the SQL options, e.g. by executing a simple query like this one ...

INSERT INTO [TargetDb].[dbo].[TargetTable] ...
  SELECT ... FROM [SourceDb].[dbo].[SourceTable]

... or something like that, if feasible. Have in mind that NO NAV Business Logic is executed when writing data with SQL!

Belias

(2) copy nav table: do you mean the data or the table structure? :?
If it's the structure: why do you need to do it by code? can't you just export from the db1 and then import to db2?
p.s.: there are import and export instructions by in C/AL, but they just handle the txtversions, thus you have to manually recompile the objects in the db2.

fazlehasan

Belias wrote:

(2) copy nav table: do you mean the data or the table structure? :?
If it's the structure: why do you need to do it by code? can't you just export from the db1 and then import to db2?
p.s.: there are import and export instructions by in C/AL, but they just handle the txtversions, thus you have to manually recompile the objects in the db2.

Thanks for your reply.

We are using classic client for NAV5.0 SP1
There is another database which is used for Web reporting by another web based application not NAV
We need to copy structure and data from NAV to WEB Database
I was wondering without writing any Stored Procedure
is it possible to send the Table - structure and data both to another database in the same server or
to a database in linked servers from NAV application.

fazlehasan

ara3n wrote:

why don't you create the table in nav and change the property, percompany to false.


If you can't you can still create the table in sql and then create a view for the table. Then in nav create the table and link it to the view.

Thanks for your reply.

I tried your solution.

It gives me "There is no table Data" Error!

This requirement of mine is due to the fact that I have already a table in SQL
in a database for another non-NAV application which is very big about 170 fields.

I want this table in NAV database - if I copy the structure through SQL
from one db to NAV db and open in NAV application this will not be available in NAV
since NAV overwrites the changes made through SQL.

What can be solution other then creating the table from NAV application manually.

fazlehasan

stryk wrote:

fazlehasan wrote:

(1) Can a table be designed first in SQL and then used in NAV C/SIDE(in the range of 50000...50999)?
We have 2 companies - I want the table designed in SQL available to both of the company
while opening in C/SIDE.

See Rashed's reply.

fazlehasan wrote:

(2) I have to copy a NAV table to another SQL database in the same server - is it possible
using C/SIDE C/AL?

No, with C/AL this is hardly possible. I suggest to look into the SQL options, e.g. by executing a simple query like this one ...

INSERT INTO [TargetDb].[dbo].[TargetTable] ...
  SELECT ... FROM [SourceDb].[dbo].[SourceTable]

... or something like that, if feasible. Have in mind that NO NAV Business Logic is executed when writing data with SQL![/quote]

Thanks for your reply.
That is my problem - when I create the table through SQL
this is not available in my NAV application,
since it is very big table with 172 fields
I was trying to avoid creating the table manually from NAV application

Belias

fazlehasan wrote:

We need to copy structure and data from NAV to WEB Database

fazlehasan wrote:

This requirement of mine is due to the fact that I have already a table in SQL
in a database for another non-NAV application which is very big about 170 fields.

I want this table in NAV database - if I copy the structure through SQL
from one db to NAV db...

Maybe it's because i'm just back from holiday, but i'm a little confused...NAV-->other not-nav db OR other not-nav db-->NAV?!

anyway, i don't know a way to "inject" a sql table in a NAV DB and make the table available to the nav client. :-k

fazlehasan

Belias wrote:

fazlehasan wrote:

We need to copy structure and data from NAV to WEB Database

fazlehasan wrote:

This requirement of mine is due to the fact that I have already a table in SQL
in a database for another non-NAV application which is very big about 170 fields.

I want this table in NAV database - if I copy the structure through SQL
from one db to NAV db...

Maybe it's because i'm just back from holiday, but i'm a little confused...NAV-->other not-nav db OR other not-nav db-->NAV?!

anyway, i don't know a way to "inject" a sql table in a NAV DB and make the table available to the nav client. :-k

Sorry may be I could not clearly state my question,

I mean to say by 'non-NAV' is that there is a db (say X) in our server
which is used for different web based application.

We have a NAV db (say Y) in which we want to create a table
with the same structure of a table which is in X
using SQL Stored Procedure and want to use that table in C/Side application.


Thanks

kine

WHat about creating view in Y connected to table in X and use it in NAV through LinkedObject property?

dmccrae

If you want to share the data between the 2 databases then LinkedObject table property is the way to go. This way it is not NAV that own's the table/view, but merely points to it.

The view in the NAV database can refer to a table in the non-NAV database. The only real problem is that of permissions - you will only be able to access the other database using a dbo user in the NAV database, since otherwise the application role used by NAV for regular users will not have permissions to objects outside of the database.

Look at this link for information about LinkedObject:

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

There is no way in NAV to programatically create tables that will also become a NAV object (you can always automate creating tables in SQL).