Transfer data from NAV to Other Database

Hi casanova,

you can link a NAV table definition to an external database table and then use INSERT in NAV.

Define a new table in NAV to represent the table in your external database. In this table's definition the property "LinkedObject" must be "yes".

You need an appropriate SQL TABLE in a database your SQL Server can access and an appropriate SQL VIEW in the NAV database.

HTH

uwe

Hi casanova,

quite close.

The way I doit is:

1. in NAV: create the table ABC in NAV as an ordinary NAV table. LinkedObject is "no".This creates a SQL table in NAV DB. Save the table and close the table editor.
2. now start SQL Server Management Console
3. find the SQL table that belongs to ABC in [NAV DB] and create a table [Copy of ABC] with exactly the same definition in [Other DB]. If you want your data stored per company you will need several tables in [Other DB]. Maybe you mangle the Company Name into the table name like NAV does.
I's a good idea to keep a copy of the script that you use to create the SQL table until you are sure that everything works fine.
4. in [NAV DB] create a VIEW into Other DB in which you select all fields of [Other DB].[Copy of ABC].
Important: the name of this view must be exactly the same as the name of the table that Navision created for your ABC table. This includes the Company Name formerly mangled into the table name if you want your data stored per company. You will Need one view per Company.

5. Got the copy of the script mentionned above in a safe place? Ok. Now delete in [NAV DB] the original SQL table in SQL Server Management Console.

Now you should have a SQL VIEW in [NAV DB] that behaves exactly like the SQL table that Navision had formerly created.

6. return to NAV designer and edit the Definition of ABC. Set LinkedObject to "yes". Save and there you are.

I hope I got the sequence of steps right in memory.