We have NAV 5.0 with 5 different databases which are 98% identical. I have challenge to consolidate several reports like, Sales, Production, Dispatches, Inventory and of course several other financial reports etc. All databases I have are native databases hence I cannot take the advantage of SQL Databases. For consolidation purpose alone I wanted to create a separate database in SQL and import all important entries on daily basis from 5 different native databases.
To achieve this I thought of 2 options:
Accessing Native Databases from SQL Through NODBC, and then writing SQL Queries to transfer data to SQL DB.
Exporting entries from Native to Excel and then Importing them to SQL Database.
I tried testing but unfortunately both of them are not working.
I am not able to access the Native database from ODBC connection.
Importing from Excel fails while transferring entries to SQL Database due to some structural, and validation issues.
I will really appreciate if someone tell me how to achieve the same.