Send Data to Other DB

hydhart
Member Posts: 20
Hi, I have a problem on sending data to another DB from NAV 2016. I have been searching that creating a view on SQL would be a solution to me. And then i try to follow the instruction :
1. in NAV: create the table ABC in NAV as an ordinary NAV table. LinkedObject is "no". This will 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(same table name, field name, data type) 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.
It'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. Now delete in [NAV DB] the original SQL table in SQL Server Management Console.
5. 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.
Query :
CREATE VIEW CRONUS International Ltd_$(table_name) AS
SELECT *
FROM [Other_DB].[dbo].[CRONUS International Ltd_$(table_name)]
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.

1e20b7336331d5789078af53.r23.cf1.rackcdn.com/vadysoft-mibuso.vanillaforums.com/editor/4k/h0j1sk6q1pzd.png
1. in NAV: create the table ABC in NAV as an ordinary NAV table. LinkedObject is "no". This will 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(same table name, field name, data type) 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.
It'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. Now delete in [NAV DB] the original SQL table in SQL Server Management Console.
5. 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.
Query :
CREATE VIEW CRONUS International Ltd_$(table_name) AS
SELECT *
FROM [Other_DB].[dbo].[CRONUS International Ltd_$(table_name)]
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.
-
On the step 6 I couldn't save the Table using Validation or Force synchronize Schema. But when I close designer and I open it again the changes been made. LinkedObject set to "Yes". I try to Run that Table and yes it worked. Data has been sent to Other DB.
The question is when i try to Sync-NAVTenant or Save again that Object it's always giving an error. Can you help me? what i'm missing?

1e20b7336331d5789078af53.r23.cf1.rackcdn.com/vadysoft-mibuso.vanillaforums.com/editor/4k/h0j1sk6q1pzd.png

0
Best Answer
-
can you save the table when LinkedObject is "no"?5
Answers
-
can you save the table when LinkedObject is "no"?5
-
Yes i can and it's worked fine. But, does that mean step 6 is wrong?0
-
no, I am checking whether table has any sync issues with linkedobject is no also.
I don't think you will get data if it is no.
0 -
when LinkedObject set to "No" yes it's not be a problem because the default value is "no". When i run the Table and input some data from NAV there is no problem at all, when i select from sql it is also showing the data i input before. so what is the use of that LinkedObject?0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions